How do I strip just numbers from an alphanumeric cell?
 

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?

How do I strip just numbers from an alphanumeric cell?
 

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


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Benjamin" wrote:

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?

How do I strip just numbers from an alphanumeric cell?
 

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

"Benjamin" wrote:

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?

How do I strip just numbers from an alphanumeric cell?
 

Maybe this formula...

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

--
Rick (MVP - Excel)


"Benjamin" wrote in message
...
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?

How do I strip just numbers from an alphanumeric cell?
 

On Mon, 5 Oct 2009 13:09:01 -0700, Benjamin
wrote:

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

How do I strip just numbers from an alphanumeric cell?
 

On Mon, 5 Oct 2009 13:44:02 -0700, ryguy7272
wrote:

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

How do I strip just numbers from an alphanumeric cell?
 

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

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

--
Rick (MVP - Excel)


"ryguy7272" wrote in message
...
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---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Benjamin" wrote:

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?