Select numbers from a string to the first alphabetical character

[gmazza76]

Good afternoon,

I have an issue that I cant currently find an answer online for.

I have a number of reports that I am analysing where the column data can have a value a numeric and alphabetical reference.

Is there a way to create a formula in PowerPivot where the column will work out the numeric characters up to the first alphabetical

Example 123456AB123 - 123456 or 123A1564 - 123

Is there away to do this in PowerPivot or would I be best doing this in a formula in Excel prior to uploading into Excel (if so what do I use)

thanks in advance

 

[peter789]

The best solution would be to load the data to Power Query. Transform the data in there then Load to the Data Model.

Peter

 

[JustynaMK]

Not sure about PowerPivot but in case no one comes back to you with the relevant answer, here's an Excel formula that you can use:

=LEFT(A1,SUMPRODUCT(SMALL(IF(CODE(MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1))),1))>=65,ROW($A$1:INDEX($A:$A,LEN(A1))),""),1))-1)

You need to enter it using Ctrl+Shift+Enter. The result:

 

[gmazza76]

thank you, this has worked in Excel, so I will just re upload the data into PowerPivot

 

[JustynaMK]

you're most welcome.

I actually just tried to do the same in PowerQuery and the task seems to be even easier:

Home > Split Column > By Digit to Non-Digit

The result:

 

[Jon von der Heyden]

Another Excel formula approach:

Formula: =LOOKUP(--REPT(9,LEN(A1)),--LEFT(A1,ROW(INDEX(A:A,1,1):INDEX(A:A,LEN(A1),1))))