Split Column on first numeric character

[Sayth]

Is there a way to split a column on the first numeric character?

I have text which has a number in a varied position in the text, however the text after it would be good in another column. Is this possible?

Example in following example both 3 and 4 appear in different positions and with a different number of preceding spaces.

[TABLE="width: 529"]
<colgroup><col></colgroup><tbody>[TR]
[TD]B G 4 Mossman x Starlevie (Fasliyev(USA))[/TD]
[/TR]
[TR]
[TD]B OR BR F 3 Teofilo(IRE) x Encapsulate (Encosta de Lago)[/TD]
[/TR]
</tbody>[/TABLE]

 

[pgc01]

Hi

To find the postion of the first digit in the text you can use:

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))

 

[Sayth]

Hi

To find the postion of the first digit in the text you can use:

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))

PowerQuery did not understand MIN.

= Table.AddColumn(#"Changed Type38", "Details", each Table.SplitColumn("Main_Race (3)",[#"description - Copy"],Splitter.SplitTextByDelimiter(MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))),2))

 

[ralliartur]

Simple function can help:

let
    Source = (string as text) => let
        Source = {0,1,2,3,4,5,6,7,8,9},
        #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "SearchIn", each string),
        #"Added Custom1" = Table.AddColumn(#"Added Custom", "Result", each Text.PositionOf([SearchIn], [Column1])),
        #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each [Result] >= 0),
        #"Grouped Rows" = Table.Group(#"Filtered Rows", {}, {{"Position", each List.Min([Result]), type number}}),
        Position = #"Grouped Rows"{0}[Position]
    in
        Position
in
    Source

 

[Sayth]

Simple function can help:

let
    Source = (string as text) => let
        Source = {0,1,2,3,4,5,6,7,8,9},
        #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "SearchIn", each string),
        #"Added Custom1" = Table.AddColumn(#"Added Custom", "Result", each Text.PositionOf([SearchIn], [Column1])),
        #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each [Result] >= 0),
        #"Grouped Rows" = Table.Group(#"Filtered Rows", {}, {{"Position", each List.Min([Result]), type number}}),
        Position = #"Grouped Rows"{0}[Position]
    in
        Position
in
    Source

Thanks I am still trying to figure out how to use this as I don't have a parameter and each time I try to create a custom function it asks for a parameter.

 

[ralliartur]

You can add this function by creating a new blank query, then go to advanced editor and paste my code -it will not ask for parameters.