Link to home
Start Free TrialLog in
Avatar of Euro5
Euro5Flag for United States of America

asked on

Extract data from concatination.

I need a formula to separate out the city names in a concatenated cell. Examples are below.
In column A row 2 & 3.
US-ALABASTER---US-IRVING
US-ATLANTA---US-JACKSONVILLE

The city name will always be after a dash.
I need in column N "ALABASTER", column O "IRVING"
colunmn N "ATLANTA", column O "JACKSONVILLE"

Can anyone help?
ASKER CERTIFIED SOLUTION
Avatar of N8iveIT
N8iveIT
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Usage:
In column N
=city1(A2)

In column O
=city2(A2)

Function City1(r As Range) As String
Dim strParts() As String

strParts = Split(r, "---")
City1 = Split(strParts(0), "-")(1)
End Function

Open in new window

Function City2(r As Range) As String
Dim strParts() As String

strParts = Split(r, "---")
City2 = Split(strParts(1), "-")(1)
End Function

Open in new window

For column N, you can use =MID(A2, FIND("-", A2)+1, FIND("---", A2)-FIND("-", A2)-1)
For column O, you can use =RIGHT(A2, LEN(A2)-SEARCH("^^",SUBSTITUTE(A2,"-","^^",LEN(A2)-LEN(SUBSTITUTE(A2,"-","")))))
Create this function:
Public Function Q_29099537(ByVal parmCellText, ByVal parmCityNumber) As String
    'parmCityNumber should be 1 or 2
    Q_29099537 = Replace(Split(parmCellText, "US-")(parmCityNumber), "-", "")
End Function

Open in new window

In N2, create this formula:
=Q_29099537(A2,1)

Open in new window

In O2, create this formula:
=Q_29099537(A2,2)

Open in new window

Fill down as necessary
Avatar of Euro5

ASKER

Sorry - thought that I completed that day! Thanks for the support!