How do I strip just numbers from an alphanumeric cell?

  • Thread starter Thread starter Benjamin
  • Start date Start date
B

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?
 
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---
 
Maybe this formula...

=LOOKUP(9.9E+307,--LEFT(TRIM(RIGHT(SUBSTITUTE(
A2,"-",REPT(" ",99)),99)),ROW($1:$99)))
 
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?
Click to expand...

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
 
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---
Click to expand...

He only wanted that final digits:

so something like

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

and

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

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

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