[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 320

# Excel Formula

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
akbiro
1 Solution

Commented:
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

Commented:
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

EngineerCommented:
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 Commented:
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

Commented:
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

Tackle projects and never again get stuck behind a technical roadblock.