Catergorization of long lists


New Member
Apr 1, 2015

I am trying to generate formats for huge keyword lists.

albany roundtrip flight
albany roundtrip flights
albany air ticket
albany air tickets
albany ticket
albany tickets
albany trip
albany trips
albuquerque airfare
albuquerque airfares
airline flight albuquerque
airline flights albuquerque
albuquerque airline ticket
albuquerque airline tickets
albuquerque airlines ticket
albuquerque airlines tickets
albuquerque airline
albuquerque airlines
flight albuquerque
flights albuquerque
fly albuquerque
last minute flight albuquerque
last minute flights albuquerque
albuquerque plane ticket
albuquerque plane tickets
albuquerque roundtrip flight
albuquerque roundtrip flights
albuquerque air ticket
albuquerque air tickets
albuquerque ticket
albuquerque tickets
albuquerque trip
albuquerque trips
where for each kw I want to replace the destination name by [Dest] and keep the rest of the keyword intact.

For example,

Albuquerque Trip - [Dest] Trip
Albuquerque Air Tickets -[Dest] Air Tickets

Please let me know if there is a way to achieve this. My list is really huge, 33000 rows.



Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Hmmm. Not sure if this will help. But you can set up equations for the "active" text in each of your phrases and just replace the city name with a asterisk. "* " if the city name is before the active text and " *" if the city name is after the active text. Then the asterisks can be replaced with city names as shown below. It would be possible to set up a shorter list of active phrases and then link them back to a list of city names - perhaps with visual basic. Anyway, it may be something to get you started.

Excel 2010
3Albany* roundtrip flightAlbany roundtrip flight
4* roundtrip flightsAlbany roundtrip flights
5* air ticketAlbany air ticket
6* air ticketsAlbany air tickets
7* tripAlbany trip
8flight *flight Albany
9fly *fly Albany
10last minute flight *last minute flight Albany
Cell Formulas
Upvote 0
Thank You. I am not sure how this would work. I have around 800 city names. I want to replace the City Name with the term [Dest] in the adjacent cell along with the rest of the phrase, like [Dest] Cheap Flights for Albany Cheap Flights.
Upvote 0
OK. try something like this. It will work as long as the cities have only a single word in their names. If multiple words, you would need to replace any spaces with underscores. But you may be able to do that with FIND/REPLACE.

Excel 2010
1OriginalConvertedTable of cities (use understores if two or more words in a city name)
2albany roundtrip flight[dest] roundtrip flightAlbany[dest]
3flight albuquerqueflight [dest]Albuquerque[dest]
4New_York trip[dest] tripNew_York[dest]
5trip New_Yorktrip [dest]
Cell Formulas
B2=IF(TYPE(VLOOKUP(LEFT(A2,FIND(" ",A2)-1),$D$2:$E$4,2,FALSE)&" "&RIGHT(A2,LEN(A2)-FIND(" ",A2)))=16, LEFT(TRIM(A2),SEARCH("-@-",SUBSTITUTE(TRIM(A2)," ","-@-",(LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ","")))/LEN(" "))) -1)&" "& VLOOKUP(RIGHT(TRIM(A2),LEN(TRIM(A2))-SEARCH("-@-",SUBSTITUTE(TRIM(A2)," ","-@-",(LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ","")))/LEN(" ")))),$D$2:$E$4,2,FALSE), VLOOKUP(LEFT(A2,FIND(" ",A2)-1),$D$2:$E$4,2,FALSE)&" "&RIGHT(A2,LEN(A2)-FIND(" ",A2)))
B3=IF(TYPE(VLOOKUP(LEFT(A3,FIND(" ",A3)-1),$D$2:$E$4,2,FALSE)&" "&RIGHT(A3,LEN(A3)-FIND(" ",A3)))=16, LEFT(TRIM(A3),SEARCH("-@-",SUBSTITUTE(TRIM(A3)," ","-@-",(LEN(TRIM(A3))-LEN(SUBSTITUTE(TRIM(A3)," ","")))/LEN(" "))) -1)&" "& VLOOKUP(RIGHT(TRIM(A3),LEN(TRIM(A3))-SEARCH("-@-",SUBSTITUTE(TRIM(A3)," ","-@-",(LEN(TRIM(A3))-LEN(SUBSTITUTE(TRIM(A3)," ","")))/LEN(" ")))),$D$2:$E$4,2,FALSE), VLOOKUP(LEFT(A3,FIND(" ",A3)-1),$D$2:$E$4,2,FALSE)&" "&RIGHT(A3,LEN(A3)-FIND(" ",A3)))
B4=IF(TYPE(VLOOKUP(LEFT(A4,FIND(" ",A4)-1),$D$2:$E$4,2,FALSE)&" "&RIGHT(A4,LEN(A4)-FIND(" ",A4)))=16, LEFT(TRIM(A4),SEARCH("-@-",SUBSTITUTE(TRIM(A4)," ","-@-",(LEN(TRIM(A4))-LEN(SUBSTITUTE(TRIM(A4)," ","")))/LEN(" "))) -1)&" "& VLOOKUP(RIGHT(TRIM(A4),LEN(TRIM(A4))-SEARCH("-@-",SUBSTITUTE(TRIM(A4)," ","-@-",(LEN(TRIM(A4))-LEN(SUBSTITUTE(TRIM(A4)," ","")))/LEN(" ")))),$D$2:$E$4,2,FALSE), VLOOKUP(LEFT(A4,FIND(" ",A4)-1),$D$2:$E$4,2,FALSE)&" "&RIGHT(A4,LEN(A4)-FIND(" ",A4)))
B5=IF(TYPE(VLOOKUP(LEFT(A5,FIND(" ",A5)-1),$D$2:$E$4,2,FALSE)&" "&RIGHT(A5,LEN(A5)-FIND(" ",A5)))=16, LEFT(TRIM(A5),SEARCH("-@-",SUBSTITUTE(TRIM(A5)," ","-@-",(LEN(TRIM(A5))-LEN(SUBSTITUTE(TRIM(A5)," ","")))/LEN(" "))) -1)&" "& VLOOKUP(RIGHT(TRIM(A5),LEN(TRIM(A5))-SEARCH("-@-",SUBSTITUTE(TRIM(A5)," ","-@-",(LEN(TRIM(A5))-LEN(SUBSTITUTE(TRIM(A5)," ","")))/LEN(" ")))),$D$2:$E$4,2,FALSE), VLOOKUP(LEFT(A5,FIND(" ",A5)-1),$D$2:$E$4,2,FALSE)&" "&RIGHT(A5,LEN(A5)-FIND(" ",A5)))
Upvote 0
Are there a limited number of non-[destination] suffixes for each entry.

i.e. 1000's of destinations, but only a few "tickets" "fly" "flight" etc...
Upvote 0
I notice in the OP data that there are both
fly albuquerque
last minute flight albuquerque

so a positional approach seems like a bad option. (i.e. I cant' trust that the first word is a city name)

Are they sorted by city?
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back