Catergorization of long lists

rohinip

New Member
Joined
Apr 1, 2015
Messages
5
Hi,

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.


Thanks
Rohini

<colgroup><col></colgroup><tbody>
</tbody>
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
CDE
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
Sheet1
Cell Formulas
RangeFormula
E3=REPLACE(D3,FIND("*",D3),1,C$3)
 
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
ABCDE
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]
Sheet2
Cell Formulas
RangeFormula
E3=E2
E4=E3
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
and
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

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

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 MrExcel.com.
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 "mrexcel.com".
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
Back
Top