How do I strip just numbers from an alphanumeric cell?

[Benjamin]

I need to strip just the numbers after the last -
RV01-HYC0246-43-T-892A i.e. 892
RV01-HYC0246-43-H-892 i.e. 892
I know how to use the Right(and find("")) commands.
Any help on just numeric character and not alphas?

 

[ryguy7272]

This is simple; somewhat crude:
Function reNums(str As String)
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\D"
reNums = re.Replace(str, "")
End Function

HTH,
Ryan---

 

[James]

check out
http://www.ozgrid.com/VBA/ExtractNum.htm

 

[Rick Rothstein]

Maybe this formula...

=LOOKUP(9.9E+307,--LEFT(TRIM(RIGHT(SUBSTITUTE(
A2,"-",REPT(" ",99)),99)),ROW($1:$99)))

 

[Ron Rosenfeld]

I need to strip just the numbers after the last -
RV01-HYC0246-43-T-892A i.e. 892
RV01-HYC0246-43-H-892 i.e. 892
I know how to use the Right(and find("")) commands.
Any help on just numeric character and not alphas?

From your example, it appears that you want a function that will "return" the
last numbers in a <space> separated string, and that this last entry might have
letters also. However, the last entry always starts with a number.

If that is the case, you can use this formula:

=LOOKUP(1E+307,--MID(TRIM(RIGHT(SUBSTITUTE(
A1,"-",REPT(" ",99)),99)),1,ROW(INDIRECT("1:10"))))

--ron

 

[Ron Rosenfeld]

This is simple; somewhat crude:
Function reNums(str As String)
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\D"
reNums = re.Replace(str, "")
End Function

HTH,
Ryan---

He only wanted that final digits:

so something like

re.pattern = ".*-\D*(\d+).*"

and

reNums = re.replace(str,"$1")

--ron

 

[Rick Rothstein]

Or, without using RegExp, this one-liner UDF...

Function FindLastNumber(S As String) As Variant
FindLastNumber = Val(Mid(S, InStrRev(S, "-") + 1))
End Function