Forum Discussion

JNelson's avatar
JNelson
Icon for Helper II rankHelper 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 Co...
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
AlienSx's avatar
AlienSx
Icon for Super User rankSuper 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
Icon for Helper II rankHelper 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!