city st zip cells

[Joanne]

My file has city st zip all in one cell, such as

Chicago IL 60000
No commas to look for to do the typical separation

I tried using =LEFT(C2,FIND(" ",C2)-1) to find the first blank space
and it works, but then some cities have 2 words in their name so that
is also no good.

I was thinking I could come in from the right, grab the zip by
locating the first space from the right and moving it, then go again
to get state, then again to get city. Problem is that I need to not
only copy the element to its own cell but to delete it from the
original cell so that I can find the next element. Can this be done,
the deletion of the elements after the copy? Or can I do it by
looking for the second blank space on the second run thru and somehow
grabbing what is between the first and second empty space?

Any help you can give me will be appreciated.

Thanks
Joanne

 

[Guest]

=MID(C2,IF(ISERROR(SEARCH(" ",C2,SEARCH(" ",C2,SEARCH("
",C2,1)+1)+1)),SEARCH(" ",C2,1),SEARCH(" ",C2,SEARCH(" ",C2,1)+1))+1,LEN(C2))


Regards,
Stefi

„Joanne†ezt írta:

 

[Guest]

The following sub routine will work for your parsing and deletion of original
data. Change the worksheet and starting cell as needed:

Sub ParseCSZ()

Dim wb As Workbook
Dim ws As Worksheet
Dim lngRow As Long
Dim strCSZ As String
Dim strCity As String
Dim strState As String
Dim strZip As String

On Error GoTo Err_ParseCSZ

Set wb = ActiveWorkbook
Set ws = wb.Worksheets("Sheet1")

ws.Activate
Range("A1").Select
strCSZ = ActiveCell.Text

Do Until strCSZ = ""
strZip = Right(strCSZ, 5)
strCSZ = Left(strCSZ, Len(strCSZ) - 6)
strState = Right(strCSZ, 2)
strCity = Left(strCSZ, Len(strCSZ) - 3)
ActiveCell.Offset(lngRow, 1).Value = strCity
ActiveCell.Offset(lngRow, 2).Value = strState
ActiveCell.Offset(lngRow, 3).Value = strZip
lngRow = lngRow + 1
strCSZ = ActiveCell.Offset(lngRow).Text
Loop
Selection.EntireColumn.Delete

Exit_ParseCSZ:

Set wb = Nothing
Set ws = Nothing
Exit Sub

Err_ParseCSZ:

Err.Clear
Resume Exit_ParseCSZ
End Sub

 

[Bob Phillips]

How about this, one hit

=LEFT(A1,LEN(SUBSTITUTE(A1,MID(A1,FIND("~",SUBSTITUTE(A1,"
","~",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),99),""))-3)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

Actually, it might be simpler if the state is always 2 and zip is always 5

=LEFT(A1,LEN(A1)-9)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Ron Rosenfeld]

My file has city st zip all in one cell, such as

Chicago IL 60000
No commas to look for to do the typical separation

I tried using =LEFT(C2,FIND(" ",C2)-1) to find the first blank space
and it works, but then some cities have 2 words in their name so that
is also no good.

I was thinking I could come in from the right, grab the zip by
locating the first space from the right and moving it, then go again
to get state, then again to get city. Problem is that I need to not
only copy the element to its own cell but to delete it from the
original cell so that I can find the next element. Can this be done,
the deletion of the elements after the copy? Or can I do it by
looking for the second blank space on the second run thru and somehow
grabbing what is between the first and second empty space?

Any help you can give me will be appreciated.

Thanks
Joanne

You could use "Regular Expressions".

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then use these formulas:

City: =REGEX.MID(A4,".*(?=(\s+\w+){2}\b)")
State: =REGEX.MID(A4,"\w+",-2)
Zip: =REGEX.MID(A4,"\w+",-1)


--ron

 

[Joanne]

Thank you, thank you

 

[Gary Keramidas]

how about using the split function, assuming your values are in column A:

Sub test2()
Dim i As Long, x As Variant
Dim lastrow As Long, cell As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For Each cell In Range("A1:A" & lastrow)
x = Split(cell, " ")
For i = 0 To UBound(x)
cell.Offset(0, i + 1).Value = x(i)
Next
Next
End Sub

 

[Gary Keramidas]

or use text to columns, that should work, too.