Forum Discussion

JNelson's avatar
JNelson
Helper II
2 years ago
Solved

Split Column to first instance of number only

Hi there!

 

I have a messy column that I need to split off the first instance of digit (the number of digits differs by each row) and leave the rest of the cell contents as is.

I tried to Split Column Digit to Non Digit which is almost there, but it splits off at every instance of digit (ie. will extend to 4 or more custom columns).

 

Example:

 

Any ideas?

 

Thanks 🙂

Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago

    hi, JNelson 

    let
    Source = table_with_messy_column,
    chars = List.Buffer({"a".."z", "A".."Z", " "}),
    split = Table.SplitColumn(
        Source, "Logistics", 
        (x) => Splitter.SplitTextByPositions(
            {0, List.PositionOfAny(Text.ToList(x), chars, Occurrence.First)}
        )(x),
        {"Custom", "Custom.1"}
    )
in
    split

4 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    hi, JNelson 

    let
    Source = table_with_messy_column,
    chars = List.Buffer({"a".."z", "A".."Z", " "}),
    split = Table.SplitColumn(
        Source, "Logistics", 
        (x) => Splitter.SplitTextByPositions(
            {0, List.PositionOfAny(Text.ToList(x), chars, Occurrence.First)}
        )(x),
        {"Custom", "Custom.1"}
    )
in
    split
    • JNelson's avatar
      JNelson
      Helper II
      2 years ago

      Hi AlienSx ,

      May be a silly question, but how do I insert that into my existing Advanced Editor?

      My last step is currently #"Removed Other Columns". I think I'm getting confused because this looks like an entire editor (with the let and in parts). 

      Thanks!