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

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

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

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?


--
Rob van Gelder - http://www.vangelder.co.nz/excel


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

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

- or -

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

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Rob van Gelder" wrote in message
...
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?


--
Rob van Gelder - http://www.vangelder.co.nz/excel


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

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

Dear Rob,

Thanks a lot for your prompt and great reply.

minus signs are not a requirement.

best regards
JIANG