Solved

# Pull City out of cell

Posted on 2014-10-10
39 Views
How do I pull out just the city of the below cell? The part in-between the commas:

28941 Canwood St, Agoura Hills, CA 91301
0
Question by:cansevin

LVL 25

Accepted Solution

ProfessorJimJam earned 500 total points
ID: 40373582
=TRIM(LEFT(RIGHT(SUBSTITUTE(A1,",",REPT(" ",100)),200),100))
0

LVL 37

Expert Comment

ID: 40373584
the city name can be easily determined by any characters between two commas of an address. trim function is also required to retreive the final result.
0

LVL 27

Expert Comment

ID: 40373805
Another possible solution:
=LEFT(MID(A1,FIND(",",A1)+2,99),FIND(",",MID(A1,FIND(",",A1)+2,99))-1)

-Glenn
0

LVL 11

Expert Comment

ID: 40373948
Hi bbao,

> the city name can be easily determined by any characters between two commas of an address.
Isn't that essentially what the questioner said in his question?  He said:
> The part in-between the commas

> trim function is also required to retreive the final result.
Not necessarily.  See Glenn's solution, for example.
0

LVL 45

Expert Comment

ID: 40374442
You might want to consider a generalized parsing function, such as this one:
``````Public Function GetAddrPart(ByVal parmAddr, parmPart As Long) As String
'parmAddr format: street, city, state zip
'parmPart values
'   1 = Street
'   2 = City
'   3 = State
'   4 = Zip
Static oRE As Object
Static oMatches As Object
If oRE Is Nothing Then
Set oRE = CreateObject("vbscript.regexp")
oRE.Global = False
oRE.Pattern = "(\S[^,]*),\s*(\S[^,]*),\s*(\w.*?) (\d+-?\d+)"
End If
Select Case parmPart
Case 1 To oMatches(0).submatches.Count
Case Else
End Select
Else
End If

End Function
``````
You can use the function in a formula, supplying which part of the parsed address you want (a value between 1 and 4)
``````=GetAddrPart("28941 Canwood St, Agoura Hills, CA 91301-1234",4)
``````
0

## Featured Post

### Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…