Results 1 to 6 of 6

Thread: Split Column on first numeric character

  1. #1
    Board Regular
    Join Date
    Jun 2010
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Split Column on first numeric character

    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.

    Code:
    B G 4 Mossman x Starlevie (Fasliyev(USA))
    B OR BR F 3 Teofilo(IRE) x Encapsulate (Encosta de Lago)

  2. #2
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,698
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Split Column on first numeric character

    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"))
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  3. #3
    Board Regular
    Join Date
    Jun 2010
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split Column on first numeric character

    Quote Originally Posted by pgc01 View Post
    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.
    Code:
    = 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))

  4. #4
    New Member
    Join Date
    Apr 2015
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split Column on first numeric character

    Simple function can help:

    Code:
    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

  5. #5
    Board Regular
    Join Date
    Jun 2010
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split Column on first numeric character

    Quote Originally Posted by ralliartur View Post
    Simple function can help:

    Code:
    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.

  6. #6
    New Member
    Join Date
    Apr 2015
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split Column on first numeric character

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •