Transforming character/number column


I have the column “Location” in my data model. I need to separate it like this:

Column A: if Location starts with a number, I need all the numbers before the first letter
Column B: I need the first letter and each letter to follow before the next number
Column C: I need all the numbers after the last letter

Just for reference:
“7A12” means my part is located in cabinet 7, drawer A, section 12 inside that drawer
“21D3” means my part is located in cabinet 21, drawer D, section 3
“MEZ” means is it up on our mezzanine
“D13” means it’s upstairs in section D, tote 13

I would like to accomplish this in the Power Query Editor. Any suggestions?


Have you tried the ‘Columns From Examples’ feature in the query editor to work through this.

I believe if you go through and work in that logic by created examples of it, it will automatically work out and do this for you.

Check out this link for more info.

It’s an amazing feature which in my view suits this problem really well.