finding numbers

[johnrb7865]

Hi, does anyone know how I would pull numbers out of a string of text? For
example, the text: Prepare Culvert Pipe (30 Inch), I need a formula to
display just the "30". The text strings will vary so I cannot use MID to pull
out text at certain spots. Any thoughts?

Thanks,
John

 

[JLGWhiz]

Try the Val function.

Sub getNumb()
Range("A1") = "Prepare Culvert Pipe (30 Inch)")
myNumb = Val(Range("A1").Value)
MsgBox myNumb
End Sub

 

[JLGWhiz]

Sorry, Val only works if the string begins with a number.

 

[Rick Rothstein]

Will the only numbers in the **always** be enclosed in parentheses? If so...

Number = Val(Mid(YourText, InStr(YourText, "(") + 1))

 

[JP]

How about a UDF that loops through the cell, uses the LIKE function to
compare each cell to [0123456789] and if there's a match, builds a
string consisting of the matching characters?

I'm sure that's the hard way.

--JP

 

[JP]

Oops it already exists

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

How about a UDF that loops through the cell, uses the LIKE function to
compare each cell to [0123456789] and if there's a match, builds a
string consisting of the matching characters?

I'm sure that's the hard way.

--JP

Sorry, Val only works if the string begins with a number.

 

[Ron Rosenfeld]

Hi, does anyone know how I would pull numbers out of a string of text? For
example, the text: Prepare Culvert Pipe (30 Inch), I need a formula to
display just the "30". The text strings will vary so I cannot use MID to pull
out text at certain spots. Any thoughts?

Thanks,
John

If the value will be the first integer in the string, then:

==========================
Option Explicit
Function Nums(str As String) As Double
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "\d+"
If re.test(str) = True Then
Set mc = re.Execute(str)
Nums = mc(0).Value
End If
End Function
===========================

If the value is might be a decimal number or a fraction, then we would need to
change re.pattern.
--ron