How to Find the position of last no

[MBS]

Hi All,
Suppose I want to find the position of last digit in a string "ABCD1234XYZ". In this case, suppose, I want to find position of "4".
Please suggest how can we do this with use of formula.

 

[bosco_yip]

Return the position of last digit in a string formula

In B1, enter formula :

=LOOKUP(9^9,0+MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),ROW(A$1:A$250))

 

[p45cal]

Late in the day, using O365, longer formula but only 1 ref to the target cell and no errors on blank cells or on cells with no numerals:
=LET(tgt,A1,rws,SEQUENCE(LEN(tgt)),MAX(IF(ISNUMBER(MID(tgt,rws,1)+0),rws)))

 

[MBS]

Return the position of last digit in a string formula

In B1, enter formula :

=LOOKUP(9^9,0+MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),ROW(A$1:A$250))

View attachment 77898

Hi bosco_yip
will you please explain me following points.
1. why we add "0" in MID results. How MID results changes by Adding 0
2. I found that, in place of 9^9, we can we "1234567890". what is your view.
3.Why we use lookup vector ROW(A$1:A$250)

 

[bosco_yip]

Hi bosco_yip
will you please explain me following points.
1. why we add "0" in MID results. How MID results changes by Adding 0
2. I found that, in place of 9^9, we can we "1234567890". what is your view.
3.Why we use lookup vector ROW(A$1:A$250)

1] 0+MID(......), in here MID(......) return text value, and 0+MID(......) convert text value to numeric value.

2] You can use 1234567890, in replace of 9^9 (387420489), but 9^9 is shorter and big enough in the formula general use.

3] Row(A$1:A$250) in provided the maximum length of a string is in 250 characters, or you can use ROW(INDIRECT("1:"&LEN(A1))),1) instead.

Regards.