Separating Words In a Long String (1 Viewer)

[RFabert]

Hello,

I need to work with columns with text data similar to the following:

United States Washington King Seattle;"United States Washington Grays Harbor Aberdeen

What I would like to be able to do is compare a list of all the counties in the state to the string and then extract the rightmost county and city listing (everything that follows the county name) into separate columns. I understand I can use a lookup table to list the counties but am not sure how to fit the lookup into the formula. In this example, I would want to extract Grays Harbor into the county column and Aberdeen into the city column.

I prefer to use functions and formulas at this point but will be converting a series of these types of manipulations into a Macro at a later time.

Thank you for your help.
RF

 

[RFabert]

This would need to work in Excel 2007

 

[NBVC]

Assuming the list of States is in F2:F51, and your first string is in A2, then first add a helper column that extracts everything after last state in your string...

in B2:

=TRIM(MID(A2,MAX(IF(ISNUMBER(SEARCH($F$2:$F$51,A2)),SEARCH($F$2:$F$51,A2)))+LEN(LOOKUP(10^10,SEARCH($F$2:$F$51,A2),$F$2:$F$51)),255))

this formula needs to be confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.

The next part is not easy because we are not sure if the county name will be 1 or 2 words or maybe the city might be 1 or 2 words... so under the assumption that the city is always 1 word, we can extract the county with this formula in C2:

=LEFT(B2,FIND("^^",SUBSTITUTE(B2," ","^^",LEN(B2)-LEN(SUBSTITUTE(B2," ",""))))-1)

and the city with this in D2:

=TRIM(SUBSTITUTE(B2,C2,""))

 

[RFabert]

Hi NBVC,

Thanks so much. I would like to clarify that there is only one state and 39 counties. Only the county and city names need to be extracted and put into separate columns. I'm assuming there's a way to compare each county name in an array or lookup to the string and then extract the county and then everything after it...starting from the right so that only the last county and city are identified and extracted.

RF

 

[RFabert]

Thank you for your help. Here are my 3 functions:

=TRIM(RIGHT(SUBSTITUTE(H2,"Washington",REPT(" ",99)),99))

=IF(VLOOKUP(LEFT(I2,FIND(" ",I2)-1),counties!$A$2:$A$40,1)=LEFT(I2,FIND(" ",I2)-1),VLOOKUP(LEFT(I2,FIND(" ",I2)-1),counties!$A$2:$A$40,1),VLOOKUP(LEFT(I2,FIND(" ",I2,FIND(" ",I2)+1)),counties!$A$2:$A$40,1))

=MID(I2,LEN(J2)+2,99)