How to extract number from cells in excel (function in Excel or VBA?)

  • Thread starter Thread starter Kortrijker
  • Start date Start date
K

Kortrijker

Dear all,

I want to get only number from cells in Excel, for example, "$123m/di" in a
cell, I want "123" left. I tried Value function in Excel, it doesn't work.

I remeber I'v seen a article before, which mentioned how to extract number
or text from cells in Excel. But I forgot how to do it totally. Or can I do
it in some function?

Thanks.
JIANG
 
I believe you could use a VBScript Regular Expression.

-or-

Sub test()
Dim str As String, i As Long, j As Long

str = "$123m/di"
i = 1: Do Until IsNumeric(Mid(str, i, 1)) Or i > Len(str): i = i + 1:
Loop
j = 1: Do Until Not IsNumeric(Mid(str, i, j)) Or i + j - 1 > Len(str): j
= j + 1: Loop
str = Mid(str, i, j - 1)

MsgBox str
End Sub

It's pretty "simple" so it won't do certain numeric things. It handles
decimal points but doesn't handle unary operators (eg. -123.4)
Let me know if minus signs are a requirement?
 
- or -

you could use strip the first bunch of alphanumerics then use the Val
Function on the rest.
 
Dear Rob,

Thanks a lot for your prompt and great reply.

minus signs are not a requirement.

best regards
JIANG
 
Back
Top