Solved

# Excel Formula

Posted on 2011-05-05
274 Views
A month or so ago a very helpful EE showed me how to write some excel formulas to strip the City, State and Zip codes from a single sell and put them into City State and Zip cells.

Attached is a list of names I am trying to reconfigure with what he showed me how to do and I can not figure it out.

I want to take the information in column E and strip out the City, State and Zip codes and put them in seperate columns.
ee-gtgx-cityStatezip.xlsx
0
Question by:akbiro

LVL 33

Expert Comment

The problem is that your data is not consistant, sometimes you have like this:

ABBEVILLE LA. 70510

others like this:

Abilene,TX 79608

So, the formula that work for one case, doesn't work for the other and vice-versa.

For Zip code you can do just like this:

=RIGHT(E2,5)

but also on this case, there are some rows that don't have a Zip code.

jppinto
0

LVL 50

Accepted Solution

I agree with jppinto, the lack of consistency in content and punctuation makes this difficult to do, but I managed some level of success......

I used these 3 formulas in F2, G2 and H2 copied down

=LEFT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2,H2,""),"."," "),","," ")),FIND(" ",TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2,H2,""),"."," "),","," "))&" ")-1)

and

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2,H2,""),F2,"",1),"."," "),","," "))

and

=IF(ISERR(RIGHT(E2)+0),"",TRIM(RIGHT(E2,LOOKUP(10^10,RIGHT(SUBSTITUTE(E2,".","x"),{1,2,3,4,5,6,7,8,9})+0,{1,2,3,4,5,6,7,8,9}))))

as you can see, the zip codes are OK, I think, but in some instances two word cities are not shown correctly.....is that some sort of start for you?

regards, barry
27019917.xlsx
0

LVL 43

Expert Comment

I would imagine that you have a list much larger than what you have uploaded. For that you would be much better off using a macro. I have written this macro which makes use of a sheet which contains a list of state names and abbreviations. This takes care of all the cases in the example file and can be tailored for more cases.

Saqib
``````Sub splitstateinfo()
For Each cel In Range("E:E").Cells
If cel.Row > 1 Then
If cel.Value <> "" Then
ts = Trim(cel.Value)
If Len(ts) > 5 Then
Select Case Mid(ts, Len(ts) - 5, 1)
Case " ", ".", ","
If Val(Right(ts, 5)) > 0 Then
cel.Offset(0, 3) = Right(ts, 5)
cel.Offset(0, 3).NumberFormat = "00000"
ts = Left(ts, Len(ts) - 6)
End If
Case Else
If Len(ts) > 10 Then
If Mid(ts, Len(ts) - 4, 1) = "-" And _
Val(Mid(ts, Len(ts) - 9, 5)) > 0 And _
Val(Mid(ts, Len(ts) - 3, 4)) > 0 Then
cel.Offset(0, 3) = Right(ts, 10)
ts = Left(ts, Len(ts) - 11)
End If
End If
End Select

End If
ts = Replace(ts, ",", " ")
ts = Replace(ts, ".", " ")
ts = Replace(ts, "  ", " ")
ts = Trim(Replace(ts, "  ", " "))
tsp = Split(ts, " ")
ls = tsp(UBound(tsp))
If cel.Offset(0, 3) = "" Then
If Len(ls) = 7 And Val(Right(ls, 5)) > 0 And _
Not Sheet2.Range("C:C").Find(Left(ls, 2), , , xlWhole) Is Nothing Then
cel.Offset(0, 3) = Right(ts, 5)
cel.Offset(0, 3).NumberFormat = "00000"
ts = Left(ts, Len(ts) - 5)
ls = Left(ls, Len(ls) - 5)
End If
End If
If Len(ls) = 2 And _
Not Sheet2.Range("C:C").Find(ls, , , xlWhole) Is Nothing Then
cel.Offset(0, 2) = ls
If Len(ts) > 3 Then
ts = Left(ts, Len(ts) - 3)
Else
ts = ""
End If
Else
If Not Sheet2.Range("A:A").Find(ls, , , xlWhole) Is Nothing Then
cel.Offset(0, 2) = ls
If Len(ts) > Len(ls) + 1 Then
ts = Left(ts, Len(ts) - Len(ls) - 1)
Else
ts = ""
End If
Else
If UBound(tsp) > 1 Then
If Not Sheet2.Range("A:A").Find(tsp(UBound(tsp) - 1) & " " & ls, , , xlWhole) Is Nothing Then
cel.Offset(0, 2) = tsp(UBound(tsp) - 1) & " " & ls
If Len(ts) > Len(tsp(UBound(tsp) - 1) & " " & ls) + 1 Then
ts = Left(ts, Len(ts) - Len(tsp(UBound(tsp) - 1) & " " & ls) - 1)
Else
ts = ""
End If
End If
End If
End If
End If
cel.Offset(0, 1) = ts
End If
End If
Next cel
End Sub
``````
Copy-of-ee-gtgx-cityStatezip.xlsm
0

Author Closing Comment

You really are a magician...that works FANTASTIC.  WOW, thank you so much.  You have done similar miracles for me in the past...we all appreciate your help.

THANKS and thanks again!!
0

LVL 50

Expert Comment

Hello akbiro, did you mean to accept my solution? I'd suggest that Saqib's macro approach is the way to go - I could probably make a formula solution with a list of states but I think this problem is better solved with VBA.....

regards, barry
0

## Featured Post

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.