Last numeric character position of a string ?

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• Jan 29, 2009

• #2

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?

 

Upvote 0

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• Jan 29, 2009

• #3

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...

 

Upvote 0

Ron Coderre

MrExcel MVP

Joined

Jan 14, 2009

Messages

2,316

• Jan 29, 2009

• #4

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:

Code:

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?

 

Upvote 0

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• Jan 29, 2009

• #5

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

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Jan 29, 2009

• #6

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.

 

Last edited: Jan 29, 2009

Upvote 0

Ron Coderre

MrExcel MVP

Joined

Jan 14, 2009

Messages

2,316

• Jan 29, 2009

• #7

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:

Code:

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:

Code:

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?

 

Upvote 0

texasalynn

Well-known Member

Joined

May 19, 2002

Messages

8,458

• Jan 29, 2009

• #8

here is a macro to find the position

Code:

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

 

Upvote 0

Ron Coderre

MrExcel MVP

Joined

Jan 14, 2009

Messages

2,316

• Jan 29, 2009

• #9

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:

Code:

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]

 

Upvote 0

Deladier

Board Regular

Joined

May 4, 2005

Messages

131

Office Version

1. 365

Platform

1. Windows

• Jan 30, 2009

• #10

Thanks

Problem solved. Thanks a lot, guys!

 

Upvote 0