CODEWITHSUNDEEP
excelstringsplit
nin-j
nin-j

Reputation: 7

excel split string by different delimiters

How would one split a cell containing string with different delimiters?

Cell A2 has "14:5-18:24" into cells b2 to e2: 14 5 18 24

Upvotes: 0

Views: 100

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

You can also do this in Power Query:

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Column1", type text}}),

    Split = Table.SplitColumn(Typed,"Column1",Splitter.SplitTextByAnyDelimiter({":","-"}))
in
    Split

To use Power Query

  • Select some cell in your Data Table
  • Data => Get&Transform => from Table/Range
  • When the PQ Editor opens: Home => Advanced Editor
  • Make note of the Table Name in Line 2
  • Paste the M Code below in place of what you see
  • Change the Table name in line 2 back to what was generated originally.

enter image description here

Upvotes: 0

chris neilsen
chris neilsen

Reputation: 53136

As a formula (requires Excel 365's Dynamic Array capability)

=IFERROR(TRANSPOSE(FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(A2,"-",":"),":","</b><b>")&"</b></a>","//b")),"")

As VBA

Sub Demo()
    Dim rSrc As Range
    Dim rDst As Range
    Dim Src As String
    Dim Result As Variant
    
    Set rSrc = ActiveSheet.Range("A2")
    Set rDst = ActiveSheet.Range("B2")
    Src = rSrc.Value2
    Result = Split(Replace$(rSrc.Value2, "-", ":"), ":")
    With rDst.Resize(1, UBound(Result) - LBound(Result) + 1)
        .Value2 = Result  ' Places result as strings
        .Value2 = .Value2 ' Convert to numbers
    End With
    
End Sub

Upvotes: 1

Related Questions