Search code examples
excelexcel-formulaoffice365

How to split a column in Excel till the first number after `*`

I am trying to split the I have range into 2. The result I am wanting is till first number after *. I have tried to do text to columns and tried using Textbefore and Textafter but it did not work because of the difference in length. There is where I am struggling.

I have I need I need
ABC*K*1234*456 ABC*K*1234 456
AB*L2*987*456*459*326 AB*L2*987 456*459*326
AB*BCD*EFGJKL*DEG*22*1971 AB*BCD*EFGJKL*DEG*22 1971
GHI*FG*HIGUI*02*1971*1985 GHI*FG*HIGUI*02 1971*1985

formula one:

=TEXTBEFORE(A1,"*",2)

formula two:

=TEXTAFTER(A1,"*",3)

Don't know why the * are removed from the markdown table screen-print in excel. Ignore the spaces in between, markdown was not reading while there were no spaces.

enter image description here

Solution

  • Formula:

    Here is one option without LAMBDA():

    enter image description here

    Formula in B2:

    =LET(s,A2:A5,z,"*",x,FILTERXML("<t><s>*"&SUBSTITUTE(s,z,"</s><s>*")&"</s></t>","//s[substring(.,2)*0=0][1]"),y,TEXTAFTER(s,x&z,1),IF({1,0},TEXTBEFORE(s,z&y),y))

    The idea here is that FILTERXML()'s xpath expression is used to identify the position of the 1st number with //s[substring(.,2)*0=0][1].

    To prevent removal of leading zero's I inserted the asterisk at position 1 in every element first. This becomes also relevant when looking to use TEXTBEFORE() and TEXTAFTER() later on to prevent false positives.

    Note: Using FILTERXML() in this context would require you to use the windows Excel 365 version.

    PowerQuery:

    Just for fun I played around in PQ to see if I could do this:

    enter image description here

    M-Code:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Out_1 = Table.TransformColumns(Source,{"I have", each let x = "*"&List.First(List.Select(Text.Split(_,"*"),(s)=> Text.Select(s,{"0".."9"})=s))&"*" in Text.ReplaceRange(_,Text.PositionOf(_,x)+Text.Length(x)-1,1,"|")}),
    Out_2 = Table.SplitColumn(Out_1, "I have", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"I need.1", "I need.2"})
in
    Out_2