seperating city and address in 1 cell 1

[smiley0q0]

i have been given a spreadsheet that has the whole address (address, city, state, zip) in one cell, i am trying to seperate them out into their own cells. the zip and state were simple, i am having trouble with the city and the address because of the different lenths of city names and not consistant with spaces...

the cell may look like this
6098 MacAshan Drive McCalla, AL 35111
or
224 DANIEL PAYNE DRIVE N. Birmingham, AL 35207
or
Hwy 30, McCammon, ID 83250

so as you can see there are multiple ways whoever the dummy was that entered all of this into one cell

anyone have any good ideas of how i can seperate the address and the city? or am i destined to do it all by hand? there are about 2000 records.
i can send a sample spreadsheet if needed.

thank you for your help.
Smiley

 

[SkipVought]

Smiley,

NOT an easy problem!

As you stated, it's easy to find State and Zip, and assuming that there is ALWAYS a comma separating city and state, you COULD get the LAST or ONLY word in the City (we could find Dallas, but San Antonio would not work)

I'f stip off the state & zip, identify the exceptions visually, and then separate the City based on classes of city formats (one word, two words, three words...)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[smiley0q0]

well... i kind of cheated on the state... they have the telephone number in another cell, so i did a search on the area code and matched it with a table i have that has all the U.S. area codes and the states they belong to. so i only got the zip code from that cell. =)
your suggestion of seperating the cities by classes, sounds like it would work, i can get the one word ones no prob., what would be the best formula to get the 2 word ones? (after i strip off the state and zip and the last coma if there is one)
and i can do a search to find 2 spaces and replace it with one space...

so i'll have cells that look like this:
2400 South 4th West Salt Lake City
or
North 400 Spokane Road, Post Falls
or
1815 North Foster Road San Antonio

Thanks,
Smiley

 

[SkipVought]

What you're gonna have to do is to group and identify the number of words to strip...

Function StripWordsFromRight(str As String, nbr As Integer) As String
    Dim ptr() As Integer
    ReDim ptr(1 To nbr)
    j = 1
    For i = Len(str) To 1 Step -1
        b = Mid(str, i, 1)
        If b = " " Then
            ptr(j) = i
            If j = nbr Then Exit For
            j = j + 1
        End If
    Next
    StripWordsFromRight = Right(str, Len(str) - ptr(nbr))
End Function

paste this in a module and use it just like a worksheet function (it is listed as a user defined function)



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[smiley0q0]

Thanks Skip,

'preciate your help. worked great.

hopefully i've made my point to a few of my other co workers how ineffective it was to put everything into one cell.

thanks again

Smiley

 

[SkipVought]

Great!

It is LUDICROUS!!!!! to jam different data elements, like...

address1, address2, City, State, Zip, First, Last etc

IN A SINGLE CELL!

If you want to do that, go use Notepad or Word.

What you have, in most cases, is a CORPORATE ASSET. Good Data is a corporate asset that can be manipulated and analyzed to glean valuable information about your business.

An employee that does not understand that is either ignorant or stupid.

Ignorance can be cured with knowledge.

Stupid is terminal!

Keep up the good fight!



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com