How can I seperate text and number from alphanumeric cell?

[Guest]

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!

 

[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!

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

 

[Guest]

Thank you so much for your help that was just what I needed.

 

[Ron Rosenfeld]

Thank you so much for your help that was just what I needed.

You're welcome. Glad to help. Thanks for the feedback.
--ron