How can I seperate text and number from alphanumeric cell?

J

Jennifer Medina

I have several addresses and I want to pull all of the numbers from the
address into a different column. The numbers are mixed in different spots and
the text to columns will not work for me. Example data:

123 S 5th Street

Need:

1235

Any help would be much appreciated!
 
R

Ron Rosenfeld

On Thu, 15 Nov 2007 13:10:14 -0800, Jennifer Medina <Jennifer
I have several addresses and I want to pull all of the numbers from the
address into a different column. The numbers are mixed in different spots and
the text to columns will not work for me. Example data:

123 S 5th Street

Need:

1235

Any help would be much appreciated!
Click to expand...

Here is a short VBA routine that should do what you want.

To enter this, <alt-F11> opens the VBEditor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

On the worksheet, enter =reNums(cell_ref) in some cell. It should return only
numbers from the string.

===============================
Option Explicit
Function reNums(str As String)
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\D"
reNums = re.Replace(str, "")
End Function
=========================
--ron
 
Top