View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld Ron Rosenfeld is offline
external usenet poster
 
Posts: 5,651
Default How can I seperate text and number from alphanumeric cell?

On Thu, 15 Nov 2007 13:10:14 -0800, Jennifer Medina <Jennifer
wrote:

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