Hi and thanks in advance!!
I have a column with 9 cells and each one has names of countries. I need to delete most of the countries but not all. I tried the find and replace but would not let me paste the list of countries I want to delete, since their are about 100 countries or more. So how can I delete some countries leaving the ones I need?
regards,
Juan
Did you try to set an autofilter on the countries you want to delete and then delete those rows?
Cheers!
Tsjallie
--------
If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!
If you think design is an expensive waste of time, try doing without ...
Sorry if I did not formulated my question ok. English is not my native.
Each cell has around 100 countries and I just need like 10 specific countries on each cell, for example:
These are the countries I need to delete on just one specific cell (with the original format):
Algeria, Algiers; Argentina, Buenos Aires; Argentina, Cordoba; Arizona, Phoenix; Australia, Brisbane; Australia, Melbourne; Australia, Sydney; Austria, Linz; Austria, Villach; Bangladesh, Dhaka; Belgium, Brussels; Belgium, Kontich; Brazil, Brasilia; Brazil, Campinas; Brazil, Sao Paulo; California, Berkeley; California, Folsom; California, Irvine; California, Los Angeles; California, Sacramento; California, San Diego; California, San Francisco; California, Santa Clara; California, Thousand Oaks; Canada, Kitchener-Waterloo; Canada, Montreal; Canada, Toronto; Canada, Vancouver; Canada, Victoria; Chile, Santiago; Colombia, Bogota; Colorado, Boulder; Colorado, Denver; Colorado, Fort Collins; Costa Rica, San Jose; Czech Republic, Prague; Denmark; Ecuador, Quito; Egypt, Cairo; England, Bristol; England, Daventry; England, Lincoln; England, London; England, Swindon; England, Winnersh Triangle; Field Sales Americas; Field Sales Americas; Finland, Helsinki; Finland, Tampere; France, Montpellier; France, Nantes; France, Nice; France, Paris; France, Toulouse; Germany, Braunschweig; Germany, Dresden; Germany, Duisburg; Germany, Karlsruhe; Germany, Koln; Germany, Munich; Germany, Nuremberg; Germany, Regensburg; Germany, Stuttgart; Germany, Ulm; Greece; Hungary, Budapest; Idaho, Boise; Illinois, Champaign; Illinois, Chicago; India, Ahmedabad; India, Andhra Pradesh; India, Bangalore; India, Chennai; India, Cochin; India, Kolkata; India, Mumbai; India, New Delhi; India, Noida; India, Pune; Indonesia, Jakarta; Indonesia, Surabaya; Ireland, Dublin; Ireland, Leixlip; Ireland, Shannon; Israel; Italy, Milan; Italy, Rome; Japan, Ibaraki; Japan, Nagano; Japan, Nagoya; Japan, Osaka; Japan, Tokyo; Jordan, Amman; Kazakhstan, Almaty; Kentucky, Louisville; Kenya, Nairobi; Lebanon, Beirut; Lithuania, Vilnius; Malaysia, Kuala Lumpur; Malaysia, Kulim; Malaysia, Penang; Massachusetts; Mexico, Guadalajara; Mexico, Mexico City; Minnesota, Minneapolis; Mongolia, Ulaanbaatar; Morocco, Casablanca; Netherlands; New Hampshire, Nashua; New Jersey; New Mexico, Albuquerque; New York, New York; New York, Poughkeepsie; Nigeria, Lagos; North Carolina, Raleigh; Northern Ireland, Belfast; Northern Ireland, Belfast; Oregon, Hillsboro; Pakistan, Islamabad; Pakistan, Karachi; Pennsylvania, Allentown; Pennsylvania, Lancaster; Pennsylvania, Philadelphia; Pennsylvania, Pittsburgh; Peru, Lima; Philippines, Cavite; Philippines, Manila; Poland, Gdansk; Poland, Warsaw; Portugal, Lisbon; PRC, Beijing; PRC, Chengdu; PRC, Chongqing; PRC, Dalian; PRC, Fuzhou; PRC, Guangzhou; PRC, Harbin; PRC, Hong Kong; PRC, Jinan; PRC, Kunming; PRC, Nanjing; PRC, Shanghai; PRC, Shenyang; PRC, Shenzhen; PRC, Urumqi; PRC, Wuhan; PRC, Xian; PRC, Zhengzhou; Romania, Bucharest; Russia, Moscow; Russia, Nizhny Novgorod; Russia, Novosibirsk; Russia, Sarov; Russia, St. Petersburg; Saudi Arabia, Riyadh; Serbia, Belgrade; Singapore; South Africa, Woodmead; South Carolina, Columbia; South Korea, Busan; South Korea, Gumi; South Korea, Seoul; Spain, Barcelona; Spain, Madrid; Spain, Seville; Sri Lanka, Colombo; Sweden; Switzerland, Zurich; Taiwan, Hsinchu; Taiwan, Kaohsiung; Taiwan, Taipei; Texas, Austin; Thailand, Bangkok; Turkey, Ankara; Turkey, Istanbul; Ukraine, Kiev; United Arab Emirates, AbuDhabi; United Arab Emirates, Dubai; Utah, Riverton; Venezuela, Caracas; Vietnam, Hanoi; Vietnam, Ho Chi Minh City; Virginia, Chantilly; Virginia, Fairfax; Washington; Washington, D.C.; Wisconsin, Eau Claire
The countries I need to keep in that cell are (which can be found in the list above):
Argentina, Buenos Aires; Argentina, Cordoba; Brazil, Brasilia; Brazil, Campinas; Brazil, Sao Paulo; Ecuador, Quito; Mexico, Guadalajara; Mexico, Mexico City; Peru, Lima; Venezuela, Caracas
hope it is clearer now!
Thanks
?????? so all those countries are in that cell,why not just paste over it?
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
:D exactly all the countries on that one cell!! and I just need to keep some of the countries there. The source of the report would not let me customize it in a way I can only pull up the countries I need, it pulls the ones I need and a lot of other countries I do not need.
Sometimes it's like 20 cells full of countries and I just need some of them.
There are a couple of cells that contains all the countries that any of the 20 cells could contain.. for example: the big list above contains the maximum of countries that the report would pull, other cells contains just some of those countries.
I just need to keep the following countries that MIGHT OR MIGHT NOT appear on each cell:
Argentina, Buenos Aires; Argentina, Cordoba; Brazil, Brasilia; Brazil, Campinas; Brazil, Sao Paulo; Ecuador, Quito; Mexico, Guadalajara; Mexico, Mexico City; Peru, Lima; Venezuela, Caracas
NOT all cells contains the above countries... for example some of them only have:
Argentina, Buenos Aires; Argentina, Cordoba; Brazil, Brasilia; Peru, Lima; Venezuela, Caracas
Last edited by Ebocaam; 09-13-2013 at 05:08 PM.
I don't think Excel offers a standard function to solve your problem.
Maybe this UDF does the trick for you.
It takes the cell containing the imported countries (as a separated string) in SrcRng, a cell containing the countries you want to keep (also as a separated string) in FltRng and the character used to separate the countries in Sep. The result of the function is a separated string containing the filtered countries.
Put the code in a module of your workbook (create it if needed) and you can use it in a formula. You'll find it under User Defined Funtions.
Usage: =FilterString(A1, B1, ";")
Please Login or Register to view this content.
Build a formula like this:
=IF(ISNUMBER(FIND("Argentina, Buenos Aires";A1))=TRUE;"Argentina, Buenos Aires";"")&... and then the same for all the other countries that you want to keep
When I say semicolon, u say comma!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks