asked on

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

jppinto

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

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Saqib Husain

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

Open in new window

Copy-of-ee-gtgx-cityStatezip.xlsm

akbiro

ASKER

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!!

barry houdini

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