Find the last number in a text string

Question

I want to find the position of the last numeric character in a text string. I'm using this formula to do so:

MAX(IF(ISERROR(FIND({1;2;3;4;5;6;7;8;9;0},A1)),"",FIND({1;2;3;4;5;6;7;8;9;0},A1))

However, this doesn't work if the string contains repeating numbers.

For instance, when the string is "10ABC2010ABC" it will return 6 instead of 9. When the string is "10ABC2131ABN" it does return 8 instead of 9.

Any ideas what's going on?

Answer 1

Here is working formula:

=MAX(IF(ISNUMBER(VALUE(MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1))),ROW(INDIRECT("1:" & LEN(A1)))))

press CTRL+SHIFT+ENTER to evaluate it.

Explanation:

• ROW(INDIRECT("1:" & LEN(A1))) returns you array {1,2,3,...,Len(A1)}
• using this array we can take each character in A1 cell: MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)
• using VALUE(...) we tries to convert each character to number. It returns #VALUE! error for all characters exept 1,2,3,4,5,6,7,8,9,0
• using ISNUMBER(...) we check whether VALUE(..) returns number or error, and if it returns number, we remember it's position.
• final step - using Max(..) we find last position of numeric character

Answer 2

FIND only finds the position of the first instance of each number so it won't work for your requirements. Try using this formula

=MAX(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},A1,ROW(INDIRECT("1:"&LEN(A1)))),0))

confirmed with CTRL+SHIFT+ENTER

That also uses FIND but the ROW(INDIRECT part starts the search further along the string on each occasion. If there are no digits in A1 you get zero as the result (you could make that an error if you want)

Another possibility if you are using Excel 2010 or later is to use AGGREGATE function like this: [untested]

=AGGREGATE(14,6,FIND({1,2,3,4,5,6,7,8,9,0},A1,ROW(INDIRECT("1:"&LEN(A1)))),1)

That doesn't require "array entry"

See here for sample workbook with suggested formulas