Last numeric character position of a string ?

[Deladier]

Hello. In cell A1 contains a string, how do I get the number of position of the last numeric character from right to left (this value will be in cell B1).

Example: 12543AR3372C31WWW (In Cell A1)

4 (In Cell B1)

Thanks in advance.

 

[Jonmo1]

How is 4 the last numerical character in the string?

12543AR3372C31WWW

As far as I can see, looking left to right, the last number is 1
Looking Right to left, the last number is 1


Ooops...I see it..

the POSITION # of the last numerical character....
Hang on, that's doable..

So last (looking Right to left) would be the same as the FIRST (looking left to right) Right?

 

[Jonmo1]

Wait, that still doesn't make sense..

the position number of the last numerical character (looking right to left) is 1
the position number of the last numerical character (looking left to right) is 14


Edit, never mind. I get it now. SHEESH!!! My head is spinning...

 

[Ron Coderre]

With
A1 containing a mixture of text and numbers...eg 12543AR3372C31WWW

This formula returns the position of the last digit on the right,counting FROM the right:

B1: =LEN(A1)+1-MAX(INDEX(ISNUMBER(--MID(A1,ROW(
INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),0))

In the above example, the formula returns: 4

Does that help?

 

[Jonmo1]

Good job Ron, I knew it was doable...

 

[Aladin Akyurek]

Try...

=LEN(A1)-LOOKUP(9.99999999999999E+307,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0,ROW(INDIRECT("1:"&LEN(A1))))+1

Shorter....

=LEN(A1)-MATCH(9.99999999999999E+307,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0)+1

which needs confirming with control+shift+enter, not just enter.

 

[Ron Coderre]

With
A1 containing a mixture of text and numbers...eg 12543AR3372C31WWW

This formula returns the position of the last digit on the right,counting FROM the right:

B1: =LEN(A1)+1-MAX(INDEX(ISNUMBER(--MID(A1,ROW(
INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),0))

EDITED TO INCLUDE THIS SHORTER ALTERNATIVE:

B1: =MATCH(1,INDEX(--ISNUMBER(--MID(A1,LEN(A1)+1-
ROW(INDIRECT("1:"&LEN(A1))),1)),0),0)

In the above example, both formulas return: 4

Does that help?

 

[texasalynn]

here is a macro to find the position

Sub FindNum()
'
'
Dim i As Long, Ln As Long, LR As Long
LR = ActiveSheet.Cells(65536, 1).End(xlUp).Row

For i = LR To 1 Step -1
    Ln = Len(Cells(i, 1))
    For j = Ln To 1 Step -1
    If IsNumeric(Mid(Cells(i, 1), j, 1)) Then
        Cells(i, 2) = Ln - j + 1
    End If
    Next j
Next i
End Sub

 

[Ron Coderre]

Hmmm...Every time I look at this thing...another approach comes to mind!

With
A1 containing a mixture of text and numbers...eg 12543AR3372C31WWW

Counting from the RIGHT, this formula returns the position of the last digit:

B1: =LEN(A1)+1-MATCH(10^99,INDEX(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),0))

In the above example, the formula returns: 4

Does that help?[/quote]

 

[Deladier]

Thanks

Problem solved. Thanks a lot, guys!