Remove non-numeric characters before first numeric character

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • 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?


    Examples:

    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


    Code
    let
    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"})
    in
    #"Removed Columns"


Participate now!

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