removing letters in vba

[brett1337]

I need to remove all the alpha text in a cell using a vba macro.

for example, cell A1 reads:

" january rate is $.045 "

Whereas I would like it to be

" $.045 "

Thank you in advance

 

[Scott Huish]

Note, you will end up with more than one leading space with this, because there are 4 spaces in your text before the $.045

Function RemoveAlpha(r As String)
With CreateObject("vbscript.regexp")
    .Pattern = "[A-Z]"
    .IgnoreCase = True
    .Global = True
    RemoveAlpha = .Replace(r, "")
End With
End Function

However, if you just want whatever the last word in the sentence is, this can be done quite easily with the following formula:

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),254))

Excel Workbook
	A	B
1	january rate is $.045	$.045
2	january rate is $.045	$.045
3
Sheet1

 

[brett1337]

Thanks I'll play with these and decide which is best.

The spaces aren't a problem, I figured they would be there.