Excel: select the last number or numbers from a cell

user_12054675 0
Viewed 1239

Given the following examples,

16A6
ECCB15

I would only like to extract the last number or numbers from the string value. So the end result that I'm looking for is:

6
15

I've been trying to find a way, but can't seem to find the correct one.

excel
2 Answers

Use thisformula:

=MID(A1,AGGREGATE(14,7,ROW($Z$1:INDEX($ZZ:$ZZ,LEN(A1)))/(NOT(ISNUMBER(--MID(A1,ROW($Z$1:INDEX($ZZ:$ZZ,LEN(A1))),1)))),1)+1,LEN(A1))

enter image description here

user_48515900

Try this:

=--RIGHT(A2,SUMPRODUCT(--ISNUMBER(--RIGHT(SUBSTITUTE(A2,"E",";"),ROW(INDIRECT("1:"&LEN(A2)))))))

or this (avoid using INDIRECT):

=--RIGHT(A2,SUMPRODUCT(--ISNUMBER(--RIGHT(SUBSTITUTE(A2,"E",";"),ROW($A$1:INDEX($A:$A,LEN(A2)))))))

Replace A2 in the above formula to suit your case.

Example

Here are the data for testing:

| String    |
|-----------|
| 16A6      |
| ECCB15    |
| BATT5A6   |
| 16        |
| A1B2C3E0  |
| 16E       |
| TEST00004 |

I have an even shorter version: --RIGHT(A2,SUMPRODUCT(--ISNUMBER(--RIGHT(SUBSTITUTE(A2,"E",";"),ROW(INDIRECT("1:"&LEN(A2)))))))

The difference is the use of SUBSTITUTE in my final formula. I used SUBSTITUTE to replace letter E with a symbol because in the fifth string in the above list, the RIGHT function in my formula will return the following: {"0";"E0";"3E0";"C3E0";"2C3E0";"B2C3E0";"1B2C3E0";"A1B2C3E0"} where the third string 3E0 will return TRUE by ISNUMBER function, and this will result in an incorrect answer. Therefore I need to get rid of letter E first.

Let me know if you have any questions. Cheers :)

user_117683640
Related