Excel Formula

Posted on 2011-05-05
Last Modified: 2012-05-11
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.
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:


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

    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)





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

    regards, barry
    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    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.

    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)
                        ts = ""
                    End If
                    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)
                            ts = ""
                        End If
                    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)
                                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


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

    Expert Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    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.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now