Counting Digits in a string

[bw]

I want to know how many digits (numbers) there are at the end of a string,
so that I may move them elsewhere.
For example:
A1= "Consider This Example489".
This example has three digits.
Because I know A1 has 3 digits (after someone explains how to compute it),
then B1=Right(A1,3)=489.

Thanks,
Bernie

 

[Tom Ogilvy]

Public Function numLen(rng as Range)
Dim sStr as String, i as Long
sStr = Range("A1").Value
for i = 1 to len(sStr)
if isnumeric(Mid(sStr,i,1)) Then
numlen = len(sStr)- i + 1
exit for
end if
Next
End Function

Demo'd from the immediate Window:
Range("B9").Value = "Consider This Example489"
? numLen(Range("B9"))
3
? Right(Range("B9"),numlen(Range("B9")))
489


or are you looking for a worksheet formula solution?

 

[Bob Phillips]

This will extract the digits at the end

=MID(A1,MIN(IF(ISERROR(1*(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1))),
255,ROW(INDIRECT("A1:A"&LEN(A1))))),99)

which is an array, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[bw]

Thank you both, Tom and Bob.
I wanted a spreadsheet solution, but I didn't know how to do it with code
either.
I don't understand the spreadsheet solution as provided by Bob, but it does
work. I'll have to see if I can figure out what you have done here to make
this work.

Thank you both again.
Bernie

 

[Bob Phillips]

Put simply, my formula looks at each character in turn and multiplies it by
1. This will error if is not a number, so it easily extracts the numeric
values.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[bw]

Thanks for the explanation Bob. It helps me to understand it somewhat, but
not enough to make a change.
The formula does not work when there are embedded numbers.
For example, "ABC 123 DEF 9".
I only want to know how many numbers are at the END of the string...in this
case 1 (the number 9).

Got a fix?

Thanks,
Bernie