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.

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




"Topher" wrote:

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.

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&"0 123456789")),ROW(INDIRECT("1:"&LEN(A1))))))
D1: =TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1,""),C1,""))

--
---
HTH

Bob

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



"Topher" wrote in message
...
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.

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

"Topher" wrote:

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.

The solution I gave you works for 41a

"Topher" wrote:

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

"Topher" wrote:

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.

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

"Topher" wrote:

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

"Topher" wrote:

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.

Thanks Mike

"Mike H" wrote:

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

"Topher" wrote:

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

"Topher" wrote:

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.

On Tue, 18 Sep 2007 01:00:02 -0700, Topher
wrote:

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.

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

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

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


"Rick Devous" wrote in message ...
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

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




"Mike H" wrote:

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




"Topher" wrote:

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.