Thread
:
How can I seperate text and number from alphanumeric cell?
View Single Post
#
2
Posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
external usenet poster
Posts: 5,651
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
Reply With Quote
Ron Rosenfeld
View Public Profile
Find all posts by Ron Rosenfeld