Address Recognition and Separation

[THE ANALYST]

Hello,
I have a large ammout of data that I'm attempting to sort by location
however the source that I receive my data from sends it to me with the names
and addresses all in one colum. I have been able to seperate, not effectivly,
the names from the addresses using text to column but I need to be able to
pull the city, state and zip into other columns without having to manually
edit each of my 6,000 per month entries. Is there any thing I can use that
will recognise the address and seperate it out... even if it is outside of
Excel?
Thank you,

 

[Ron Rosenfeld]

On Thu, 28 Feb 2008 12:30:01 -0800, THE ANALYST <THE

Hello,
I have a large ammout of data that I'm attempting to sort by location
however the source that I receive my data from sends it to me with the names
and addresses all in one colum. I have been able to seperate, not effectivly,
the names from the addresses using text to column but I need to be able to
pull the city, state and zip into other columns without having to manually
edit each of my 6,000 per month entries.

Is there any thing I can use that will recognise the address and seperate it out
... even if it is outside of Excel?

Yes, there is.

But how to do that depends on the format of your data. So you will have to let
us know that.
--ron

 

[Gary''s Student]

Post sample of your data to give us some guidance.

 

[Billy Liddel]

Hi

You might like to use this UDF.

Function TEXT2COL(ByVal txt, Optional start As Integer, Optional q As
Integer) As String
Dim tmp As String, i As Integer, str() As String
txt = Application.Substitute(txt, ",", "")
str() = Split(txt, " ")
If start = 0 Then
'Just remove commas
TEXT2COL = txt 'this is OK
ElseIf start >= 1 And q > 1 Then
For i = start - 1 To start + q - 2
tmp = tmp & str(i) & " "
Next i
TEXT2COL = Trim(tmp)
Exit Function
ElseIf start >= 1 And q = 0 Then
TEXT2COL = str(start - 1)
End If
End Function

It works like Excels Mid function except in words rather than characters

Example Data
James Graham, 16 the Low Road

Results and formula enter
James Graham 16 The Low Road =PROPER(TEXT2COL($A$15))
James Graham =TEXT2COL($A$18,1,2)
16 The Low Road =TEXT2COL($A$18,3,4)
James =TEXT2COL($A$18,1)
Graham =TEXT2COL($A$18,2)

Regards
Peter