Find phrases in Excel

Sometimes finding words in cells does not answer all questions, because the word itself does not always carry an unambiguous meaning.

For example, phrasal verbs are the case where a word written after another, changes its meaning completely. However, second word in them is usually a very short word, that can easily be the beginning of another word. For example, “Made it uphill”, “show offensive…”. This is what makes the search for phrases a tricky job in Excel. There are also some other things that might be a hindrance:

• Punctuation
• Excessive spaces in cells
• The number of phrases to search (it might just be big)

!SEMTools macros for searching, extracting and deleting phrases in cells were designed exactly for this kind of cases. They ignore simple substring rules and search for exact word sequences.

Find toponyms (cities, countries etc.) in text

Most city names contain only 1 word (Chicago), but some of them contain 2 or more (Salt Lake City, Los Angeles etc.). Also, there are lots of them, and it’s not that easy to get the full list.

Imagine a national retail chain with outlets in various cities and towns that wants to target potential customers through localized SEO and PPC campaigns. SEM (Search Engine Marketing) professionals compiled a list of toponyms that includes cities, neighborhoods, and popular local landmarks where they have a presence.

Their objective is to boost online visibility for each of their retail locations by creating content and ads tailored to local audiences, using specific toponyms relevant to each store.

Here is the scenario where they would definitely need to search for toponyms among their search queries:

1. Data Compilation: The marketing team compiles an Excel spreadsheet containing:
   • Store IDs.
   • Associated cities and towns.
   • Nearby neighborhoods and landmarks.
   • Historical PPC campaign performance metrics and organic search rankings for each toponym.
2. Toponym Search:
   • The team wants to review their SEO and PPC performance in key urban areas first. They’ll use Excel to search for specific toponyms related to major cities.
   • By searching for multiple city names simultaneously (e.g., “New York,” “Los Angeles,” “Chicago”), they can quickly filter out relevant rows and analyze the performance metrics for those locations.
3. Content & Ad Creation:
   • Based on their findings, they identify which major cities need more localized content or ad campaigns.
   • For example, if the New York outlets aren’t ranking well for local search terms or aren’t receiving satisfactory PPC performance, the team decides to create blog posts about local events in New York, new product arrivals specific to their New York stores, or PPC ads highlighting a New York-specific promotion.
4. PPC Keyword Expansion:
   • While analyzing the spreadsheet, they notice that some neighborhoods or landmarks (like “Brooklyn” or “Central Park”) perform particularly well in driving traffic.
   • The team decides to expand their PPC keyword list to include more local terms associated with those toponyms. For instance, they might create PPC campaigns for “Retail Chain near Central Park” or “Brooklyn Retail Chain Deals.”
5. Performance Review:
   • After implementing the localized campaigns, the team updates the Excel spreadsheet monthly with new performance metrics.
   • They continue to use the multi-toponym search feature to assess the success of their efforts, compare different locations, and decide where further content or PPC adjustments are needed.

In this use case, Excel’s ability to search for multiple phrases (toponyms) in cells becomes invaluable for businesses with multiple locations, enabling them to analyze large data sets quickly and make data-driven decisions for their SEO and PPC efforts.

Unfortunately, there’s no inbuilt option for that in Excel now. Although, !SEMTools adds these instruments, too! The add-in knows about 10.000 most popular geographical names, including cities, countries, isles etc.

With the add-in, you can easily find and extract toponyms from search queries or any other data:

As you see, true-false search makes it very easy to filter queries that contain toponyms. But it’s not easy to see what toponym exactly was found in cell. That’s why you might also want to extract toponyms from cells. Which is what you see on the animation above.

Find own list of phrases in range

Here are some situations where you would want to find a list of specific phrases in Excel cells at once:

1. Content Audits and Quality Control:
   • Scenario: A publishing firm has a massive Excel spreadsheet containing thousands of articles’ titles, descriptions, and keywords. Before publishing, the firm needs to ensure that certain sensitive or potentially offensive phrases are not included.
   • Use: By searching for multiple specific phrases at once, the firm can quickly identify and correct or remove inappropriate content.
2. Inventory Management:
   • Scenario: A large retailer uses an Excel sheet to manage its inventory. The retailer is holding a special promotion targeting specific brands and product categories.
   • Use: The retailer can search for multiple specific product names or brands simultaneously, allowing for quick identification, categorization, and analysis of promotional products.
3. Regulatory Compliance:
   • Scenario: A financial institution stores transaction details in Excel. To comply with financial regulations, they need to ensure that certain types of transactions or flagged terms are reviewed.
   • Use: By searching for a list of specific phrases that are indicators of potential non-compliance, the institution can proactively address and resolve issues.
4. Academic Research Analysis:
   • Scenario: A researcher has compiled a database of historical documents in Excel. They want to analyze the frequency and context of certain phrases related to societal trends.
   • Use: By searching for multiple phrases at once, the researcher can rapidly identify and analyze relevant documents, aiding in their qualitative analysis.
5. Customer Feedback and Reviews:
   • Scenario: A company collects customer feedback in an Excel spreadsheet. They’re interested in quickly identifying common pain points or praises.
   • Use: By searching for a list of specific phrases or keywords, the company can quickly gauge the sentiment of their customer base and take appropriate action.
6. Recruitment:
   • Scenario: An HR department has resumes and CVs stored in Excel. They’re recruiting for multiple positions and have certain qualifications or keywords they’re looking for.
   • Use: By searching for multiple phrases or qualifications at once, HR can efficiently shortlist candidates who match the job requirements.

Look at the example of using the search by a defined list of phrases with !SEMTools:

When executed, this macro can search for both phrases and words. However, if you are sure that you can get by with a single word search, it is preferable to use it: on volumes of tens or hundreds of thousands of lines, the difference in processing speed will be noticeable.

It is recommended to firstly remove all characters except numbers and letters, so that phrases with punctuation would still be found by the script.