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

Try the Val function.

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



"johnrb7865" wrote:

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

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

"johnrb7865" wrote:

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

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

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

--
Rick (MVP - Excel)


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

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


On Sep 22, 4:45*pm, JLGWhiz wrote:
Sorry, Val only works if the string begins with a number.



"johnrb7865" wrote:
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- Hide quoted text -

- Show quoted text -

Oops it already exists :-)

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


On Sep 22, 4:54*pm, JP wrote:
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

On Sep 22, 4:45*pm, JLGWhiz wrote:



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

On Mon, 22 Sep 2008 13:23:01 -0700, johnrb7865
wrote:

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