Euro5
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-JACKSONVIL LE
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?
In column A row 2 & 3.
US-ALABASTER---US-IRVING
US-ATLANTA---US-JACKSONVIL
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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("^^",SUBSTI TUTE(A2,"- ","^^",LEN (A2)-LEN(S UBSTITUTE( A2,"-","") ))))
For column O, you can use =RIGHT(A2, LEN(A2)-SEARCH("^^",SUBSTI
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
In N2, create this formula:=Q_29099537(A2,1)
In O2, create this formula:=Q_29099537(A2,2)
Fill down as necessary
ASKER
Sorry - thought that I completed that day! Thanks for the support!
In column N
=city1(A2)
In column O
=city2(A2)
Open in new window
Open in new window