Power Query - Spilt Column From Digit to Non-digit Once

[qinh_ann]

Hello,

I need help with Power Query for something. I have a column that has both addresses and 6-7 digits student ID at the end of each row for that column, and I need to split them out into two column, one contain only addresses and one only student ID.

I figured using Split column from digit to non-digit would be perfect for this case but I don't know how to adjust the query so that it would split the column right to left instead, and I only need it to do the split once since the addresses also have number in it.

Example:

Address & ID

ABC 3 DEF Road 123456

=>

Address	ID
ABC 3 DEF Road	123456

Does anybody have any idea how I can make this happen or is this even possible within Power Query?

Thanks in advance, help is much appreciated!!

[Ahmedx]

pls try this

[AlexisOlson]

How about splitting by the first space from the right side? You can do that with just the GUI.

You could also do it as two custom columns.

Address = Text.TrimEnd([#"Address & ID"], {"0".."9", " "})

ID = Text.AfterDelimiter([#"Address & ID"], [Address])

Full sample query:

let
    Source = Table.FromRows({{"ABC3 DEF Road 123456"}}, {"Address & ID"}),
    #"Added Address" = Table.AddColumn(Source, "Address", each Text.TrimEnd([#"Address & ID"], {"0".."9", " "})),
    #"Added ID" = Table.AddColumn(#"Added Address", "ID", each Text.AfterDelimiter([#"Address & ID"], [Address]))
in
    #"Added ID"

[rsbin]

@qinh_ann ,

If the ID is always 6 digits use:

=text.end( [Address & ID], 6).

Then you can Trim the address column to get rid of the last " ".

Happy Holidays.

[qinh_ann]

Thanks for the help, but unfortunately some of them is 6 digits and some is 7 digits so I need to do split from digit to non-digit instead 😞

Happy holidays!!

[rsbin]

@qinh_ann ,

There are a myriad of text functions available to you.
https://learn.microsoft.com/en-us/powerquery-m/text-functions
You just need to find a common characteristic that helps identify where the ID starts. 

For example:  Do they always start with a "1" or "12"?  Does the Address always end with "Road"?
You can substitute the " " (space) between the end of the Address and the start of the ID with another character ( "~"), then use that as your Delimiter.
You just have to get creative to find the right combination that will work for your various scenarios.
Regards,