Extract Last Number From String in Excel and in Power Query

Jon Acampora's challenge to write a formula identifying?Even or Odd License Plates is fun. But how do you extract numbers from a string? In Excel there is always a way, …

Here is a formula suggestion from Ablebits: =IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)) * ROW(INDIRECT("1:"&LEN(A2))), 0), ROW(INDIRECT("1:"&LEN(A2))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A2)))/10) ,"") ??

Wow, even advanced Excel users would struggle to understand this solution. The easiest part is that the formula is wrapped into an IF function which tests if any number is contained and gives "" if none is contained.

And here is what you need in Power Query: =Text.Select([ColumnName], {"0".."9"}) ??

If you know nothing about Power Query you still understand it. And if there is no number in the string you get an empty field which is correct. ?

Text.Select is an incredible useful function, it is worth learning it! I do recommend this article from Phil Treacy: https://www.myonlinetraininghub.com/extract-letters-numbers-symbols-from-strings-in-power-query-with-text-select-and-text-remove

How to use Text.Select to determine if a license plate is even or odd?

With = Text.End(?Text.Select([Plate Number], {"0".."9"}), 1) you could extract the last number in Power Query, but we can instead evaluate the entire number.

First make sure that the data type is set to type text to avoid any errors with pure numbers. Then you can add a Custom Column with this formula:

= if?Number.IsEven(

?????????????????Number.From( "1"& Text.Select([Plate Number], {"0".."9"}) )

???????????)

???then "Even"

???else "Odd"

From inside out the formula works like this:

1. Text.Select extracts all values from the string which are equal to 0 - 9
2. "1"& in front ensures that plates without numbers, will be evaluated as odd
3. Number.From converts the text value into a number

Then you test with Number.IsEven in an if function if the number is even or not.

It is easier than any Excel formula you'd need for this task. And if you don't like nesting formulas you can also do it step by step (with more UI support). Last but not least: Whatever you learn in Power Query you can use in Excel and Power BI.

How to determine if a license plate is even or odd with an Excel formula?

I wrote "in Excel there is always a way", but that is wrong: In Excel there are always many ways. Pick one which is not overly complex and which you can understand when you read it. This was my approach:

= IFERROR(

IF( ISEVEN(

MID(A5; MAX(ISNUMBER(VALUE(MID(A5;{1;2;3;4;5;6;7;8;9;10};1)))*{1;2;3;4;5;6;7;8;9;10}); 1));

);

"Even";

"Odd");

"Odd")

[In the US with a comma instead of the semicolon.]

Here is what the formula does from inside out with the license plate "123M57":

1. The inner MID function extracts the first 10 values* of cell A5, one character at a time. The result is an array: {"1";"2";"3";"M";"5";"7";"";"";"";""}
2. The VALUE function converts the numbers in text format to actual numbers: {1;2;3;#VALUE!;5;7;#VALUE!;#VALUE!;#VALUE!}
3. ISNUMBER gives: {TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE}
4. Then multiply that times another array with 10 numbers to get: {1;2;3;0;5;6;0;0;0;0}
5. MAX gets the largest value, which is the position of the last number: 6
6. This position is used in the outer MID function to extract the last number: 7

*The hard-coded number array handles raw values up to 10 characters in length. You can expand this but the assumption is that 10 characters are sufficient for license plates.

Then you test with ISEVEN in an IF function if the last number is even or not.

Finally you wrap this in an IFERROR to handle cases without any number.

Or wrap Abelbit's general formula like this to evaluate the last number of the string: =IFERROR(IF(ISEVEN( RIGHT(IF(SUM(LEN(A5)-LEN(SUBSTITUTE(A5, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A5, LARGE(INDEX(ISNUMBER(--MID(A5,ROW(INDIRECT("1:"&LEN(A5))),1))* ROW(INDIRECT("1:"&LEN(A5))),0), ROW(INDIRECT("1:"&LEN(A5))))+1,1) * 10^ROW(INDIRECT("1:"&LEN(A5)))/10),""),1)) ,"Even","Odd"),"Odd")

=> And there are many more (shorter) to choose from in ?Even or Odd License Plates. ??

My favourite is from Bill Szysz, a "Power Query poet" and a genius with formulas:

=IF(

LOOKUP(2; MOD(--MID(1&A5;ROW($1:$10);1);2));

"Odd";"Even")

Here is what this short formula does from inside out with the license plate "123M57":

1. The MID function extracts the first 10 values* of 1 concatenated with cell A5. The added 1 deals with cases without number:{"1";"1";"2";"3";"M";"5";"7";"";"";""}
2. The double negative forces the values to be numbers: {1;1;2;3;#VALUE!;5;7;#VALUE!;#VALUE!;#VALUE!}
3. MOD returns the remainder after dividing with 2 [0s were even, 1s were odd]: {1;1;0;1;#VALUE!;1;1;#VALUE!;#VALUE!;#VALUE!}
4. LOOKUP?ignores errors, so LOOKUP will scan through the 0s and 1s looking for a 2 and never find it . When it reaches the end of the array, it will go back and match to the next smaller value that is not an error:?i.e. the?last 0 or 1 in the array. [This works because LOOKUP performs an approximate match assuming that the values are sorted in ascending order. The initially concatenated 1 makes sure that there is no error, there is always a 1 in the beginning even if there were no numbers in the license plate.]: 1

*ROW($1:$10) handles license plates up to 9 characters in length. You can easily expand this to e.g. ROW($1:$20) or replace it by a flexible SEQUENCE(LEN(A5)+1.

Finally you wrap this in an IF function cleverly using the resulting 1 (TRUE) or 0 (FALSE) to determine "Odd" or "Even".

Wow, this is pure genius! Anyone with a better solution?

_____________________________________________

Last but not least: If you just want to extract numbers, there is a very nice and short formula solution from Meni Porat:

=CONCAT(IFERROR(--MID(A5;SEQUENCE(LEN(A5));1);""))

Questions and suggestions:?https://www.dhirubhai.net/in/matthiasfriedmann

?Let?me?help?you?with?your?data!

"LIKE", "COMMENT" or "SHARE" this article,?to give your network a chance to find it!