I have a table field where the data contains our memberID numbers followed by character or character + number strings For example:
My Data 1234567Z1 2345T10 222222T10Z1 111 111A Should Become 123456 12345 222222 111 111
I want to get just the member number (as shown in Should Become above). I.E. all the digits that are LEFT of the first character. As the length of the member number can be different for each person (the first 1 to 7 digit) and the letters used can be different (a to z, 0 to 8 characters long), I don't think I can SPLIT the field.
Right now, in Power Query, I do 27 search and replace commands to clean this data (e.g. find T10 replace with nothing, find T20 replace with nothing, etc)
Can anyone suggest a better way to achieve this?
I did successfully create a formula for this in Excel...but I am now trying to do this in Power Query and I don't know how to convert the formula - nor am I sure this is the most efficient solution.