Remove non-numeric characters before first numeric character

  • I have a column with street addresses but some of them have a name in front of the address.

    How do I remove the non-numeric characters before the first numeric character?


    Debbie9676 S. Mount Jordan Rd

    Ivory Ridge Office3401 N. Center St. #250

    June3535 E. Little Cottonwood Ln 84

  • With Power Query, here is the Mcode

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Character Transition" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Column1.1", "Column1.2", "Column1.3"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Character Transition",{"Column1.2", "Column1.3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Column1.1"})
    #"Removed Columns"

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!