Pull City out of cell

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.

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

-Glenn

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
Did you read the question?

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

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
    If oRE.test(parmAddr) Then
        Set oMatches = oRE.Execute(parmAddr)
        Select Case parmPart
            Case 1 To oMatches(0).submatches.Count
                GetAddrPart = oMatches(0).submatches(parmPart - 1)
            Case Else
                GetAddrPart = vbNullString
        End Select
    Else
        GetAddrPart = vbNullString
    End If
    
End Function

Select allOpen in new window

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)
=GetAddrPart(B2,4)

Select allOpen in new window