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

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

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
Asked:
akbiro
1 Solution
 
jppintoCommented:
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
 
barry houdiniCommented:
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
 
Saqib Husain, SyedEngineerCommented:
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
0
 
akbiroAuthor 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
 
barry houdiniCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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