Extracting numbers from text string

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Topher - 18 Sep 2007 09:00 GMT

I have to import an address file into an excel sheet (I can do that) but one
of the columns in the file is a text field that contains the important house
number mixed with text:

The Old Vicarage 29 The High Street
T & G Plumbers 30 Long Road
The Corner Shop 2 Village Green

These text strings need splitting into 3 separate cells, data before the
number, the number, and data after the number. Can anyone help please.

Reply to this Message

Mike H - 18 Sep 2007 10:02 GMT

Hi,

Using the function here to find the number

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

Text to the left can then be extracted with
=LEFT(A1,FIND(ExtractNumber(A1),A1)-2)

and text to the right of the number with
=MID(A1,(FIND(" ",A1,FIND(ExtractNumber(A1),A1))+1),999)

Mike

> I have to import an address file into an excel sheet (I can do that) but one
> of the columns in the file is a text field that contains the important house
[quoted text clipped - 6 lines]
> These text strings need splitting into 3 separate cells, data before the
> number, the number, and data after the number. Can anyone help please.

Reply to this Message

patbaeske - 07 Nov 2008 21:05 GMT

I'm trying to separate the house number from the rest of the address within a
column and I can't follow what you say to do. Can you give it to me in exact
steps (i.e., click this and insert that) and send it to my email address?

patbaeske at charter dot net

> Hi,
>
[quoted text clipped - 20 lines]
> > These text strings need splitting into 3 separate cells, data before the
> > number, the number, and data after the number. Can anyone help please.

Reply to this Message

Bob Phillips - 18 Sep 2007 10:04 GMT

B1: =TRIM(LEFT(A1,FIND(C1,A1)-1))
C1:
=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1))))))
D1: =TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1,""),C1,""))

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I have to import an address file into an excel sheet (I can do that) but
>one
[quoted text clipped - 8 lines]
> These text strings need splitting into 3 separate cells, data before the
> number, the number, and data after the number. Can anyone help please.

Reply to this Message

Rick Devous - 31 May 2008 12:32 GMT

I used your formula and it works great! However I have address I am trying to sort and some have an apartment number, so perhaps 101 green street #16 could be turned into 101.16. Then all of the apartments in the same address would sort in order. Any Ideas?
Thanks
Rick

Reply to this Message

Norman Jones - 31 May 2008 13:23 GMT

Hi Rick,

As you have opened a new, orphaned,
thread, it is difficult for the original
respondent to assist you further and no
context to your question is available to
prospective new respondents.

Consider, therefore, reposting your
question in the original thread.

---
Regards.
Norman

>I used your formula and it works great! However I have address I am trying
>to sort and some have an apartment number, so perhaps 101 green street #16
>could be turned into 101.16. Then all of the apartments in the same address
>would sort in order. Any Ideas?
> Thanks
> Rick

Reply to this Message

Topher - 18 Sep 2007 10:22 GMT

Thanks for that guys but to add to the problem becuase I am looking at
address data there are times when the number could be associated with a
letter i.e. 41a High Road. Is there a way to deal with this aswell?

Thanks

Reply to this Message

Mike H - 18 Sep 2007 10:36 GMT

The solution I gave you works for 41a

> Thanks for that guys but to add to the problem becuase I am looking at
> address data there are times when the number could be associated with a
[quoted text clipped - 12 lines]
> > These text strings need splitting into 3 separate cells, data before the
> > number, the number, and data after the number. Can anyone help please.

Reply to this Message

Mike H - 18 Sep 2007 10:38 GMT

No it doesn't!!

Use the function but to extract a number such as 41a use

=MID(A1,FIND(ExtractNumber(A1),A1),(FIND("
",A1,FIND(ExtractNumber(A1),A1)))-FIND(ExtractNumber(A1),A1))

Mike

Reply to this Message

Topher - 18 Sep 2007 10:46 GMT

Thanks Mike

> No it doesn't!!
>
[quoted text clipped - 21 lines]
> > > These text strings need splitting into 3 separate cells, data before the
> > > number, the number, and data after the number. Can anyone help please.

Reply to this Message

Ron Rosenfeld - 18 Sep 2007 11:46 GMT

>I have to import an address file into an excel sheet (I can do that) but one
>of the columns in the file is a text field that contains the important house
[quoted text clipped - 6 lines]
>These text strings need splitting into 3 separate cells, data before the
>number, the number, and data after the number. Can anyone help please.

Here's a UDF that should handle that:

To use it, enter a formula of the type:

=parseaddr(cell_ref,Index)

where cell_ref contains your original string and Index is a number from 1 to 3
indicating which section of the address you wish to pull out (see the comment
in the UDF).

To enter the UDF, <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:

=========================================
Option Explicit
Function ParseAddr(str As String, Index As Long) As String

'Index: 1 = part before street number
' 2 = street number with optional letter
' 3 = part after street number

Dim re As Object
Dim mc As Object
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.Global = True
re.Pattern = "^((\D+)(\s))?((\d+[A-Z]?)(\s))(.*$)"
If re.test(str) = True Then
Set mc = re.Execute(str)
Select Case Index
Case Is = 1
ParseAddr = mc(0).submatches(1)
Case Is = 2
ParseAddr = mc(0).submatches(4)
Case Is = 3
ParseAddr = mc(0).submatches(6)
Case Else
ParseAddr = ""
End Select
End If
End Function
==============================================

--ron

Reply to this Message