We’ve already discussed how to delete words in Excel, even the shortest ones from cells. However, you often need to delete phrases, not just words. The usual replacement methods might leave behind parts of words, such as endings or beginnings.
!SEMTools is ideal for handling these challenges. It doesn’t just look at a string of text as a substring but focuses on individual words and compares them during a search. For phrases, the tool looks for consecutive words that match the order in the search phrases. If it finds a complete match, it deletes the entire phrase. If it only finds part of the phrase, it does nothing.
Delete cities (toponyms)
Toponyms, which include city and place names, are commonly deleted from texts. You can remove them along with prepositions like “in Salt Lake City” or “in New York,” or just delete the toponyms themselves. This helps avoid leftover prepositions.
When handling large datasets, it’s crucial to check if the list contains any city names first. After identifying them, you should extract these phrases. This step ensures you don’t accidentally remove essential information, as sometimes toponyms look the same as common words.
Here are some examples:
- Reading – A city in England, and also the common noun describing the act of interpreting written text.
- Nice – A city in France, pronounced /nees/, but easily confused with the English adjective meaning pleasant.
- Mobile – A city in Alabama, USA, and the adjective relating to being movable or the technology of mobile phones.
- Turkey – A country in Eurasia, and the common name for a large bird often associated with American Thanksgiving.
- Lima – The capital city of Peru, and a type of bean widely used in cooking.
- Orange – A city in California, USA, as well as a fruit and a color.
- May – A small town in Oklahoma, USA, and the fifth month of the Gregorian calendar.
- China – A country in Asia, and also a term for dishes made of porcelain.
- Phoenix – The capital city of Arizona, USA, and a mythical bird known for rising from its ashes.
- Paris – The capital city of France, but also a small city in Texas, USA, among other places around the world.
Watch these brief videos to see how toponyms are deleted from a dataset. First, cities with prepositions before them are deleted:
Next, we delete cities without prepositions:
Delete your own list of phrases
To delete your own list of phrases, there is a separate menu item. The only requirement is that the list should be in one column.
It is important that it is not necessary to have only phrases in the list — individual words will also be deleted. But the macro should be used if there is at least one phrase in the list of words.