Excel Find Partial Text Strings

This is a guest tutorial written by Ruthie Ward of Idaho, USA.

I wanted to find a partial match to a text string in a long list of words without having to scroll down and look at each one. I also wanted to know the number of possible matches before I filtered the list.

Using the functions SEARCH (not case-insensitive) and ISNUMBER (did I find a match?) in combination with an IF statement, I can evaluate every word in the list with minimal keystrokes.

I’ve added a user interface to my “Simple Rhyme-Finder”. You can download the Excel file here that shows the technique, which can also be applied to finding multiple partial strings in a list. More on that later.

Word List

I started with a list of a couple of hundred adjectives. You can use your own list and the file is flexible enough to use another language, such as Spanish, that uses Latin characters.

By entering a word in the orange cell (C3) and the number of letters in the rhyming part of your word in the grey box (C4), it’s easy to retrieve all possible rhymes in the word list.

I entered “harpy” as the word to be rhymed and “2” as the number of letters in the rhyme and then filtering the “Possible Rhymes” for non-zero results, and got 5 possible rhymes.

How it Works

We use the RIGHT function to extract the 'Rhyme Part' in cell C6. The RIGHT function returns the specified number of characters from the end of a text string. The syntax is:

RIGHT(text, number of characters)

The formula in cell C6 is:

=""""&RIGHT(C3,C4)&""""

In English it reads:

Put in a double quote mark then take the right portion of the value in C3 for the number of characters in cell C4 and then follow it with another double-quote mark.

This explicitly shows the user what is being rhymed (searched) in cell C6. Note: The double quotes around the RIGHT formula are just for formatting purposes.

To check each word in the list, I use a formula in column C to check if the ‘rhyme part’ exists:

=IF( ISNUMBER( SEARCH( RIGHT($C$3, $C$4) ,RIGHT(B10,$c$4)) ),1 ,0 )

In English:

=SEARCH the two RIGHT-most characters in cell B10 for the RIGHT 2 letters in cell C3, if they are found the SEARCH function returns a number and ISNUMBER results in TRUE, the IF function will therefore return a 1, otherwise it will return a 0.

i.e. If the last two characters in the word in B10 contain the letters in C6, the formula equals 1, otherwise it equals 0. The cell references for C3 and C4 are absolute (using the $) to allow the formula to be copied down the column and stay the same for the entire word list.

Note: this ISNUMBER(SEARCH... formula could be simlified to:

=IF(RIGHT($C$3,$C$4)=RIGHT(B10,$C$4),1,0) or =--(RIGHT($C$3,$C$4)=RIGHT(B10,$C$4))

But for consistency with the next example we're using the ISNUMBER & SEARCH combination.

Next I wanted to count the number of possible rhymes (in C7) so I can see if there are zero rhymes or not. For this I used the formula:

=SUM(C10:C218) i.e. =SUM(top:bottom)

This simply adds all the values where the rhyme part was found in the word for the whole list.  Since a possible match is marked with a 1, I just sum the list of values to get the number of possible matches.

Other Uses - Finding Partial Matches

Finding a rhyme may seem an impractical use, but it does demonstrate my technique.

With a slight modification and by entering your own list (part numbers, descriptions, names, etc.), you can turn the rhyme-finder into a search tool to find partial matches.

For example, when you know the name of customer, but aren’t sure how they are listed.  Is it “KLJ Company” or “K. L. J. Co.”?  In the example below, the search results are “K. L. J. Company” and “Killian Lynn Jergen’s Corp.” because these are the only 2 customers in the list that have all the letters “K”, “L”, and “J” in the right order.

Using this type of finder you can easily find out if a customer you think is in the list even if the name has been misspelled.

In cell J7 we've inserted the asterisk wildcard between the search characters to specify that we want to find the letter followed by any other characters.

Download

Thanks

A big thank you to Ruthie for writing this tutorial and sharing her Rhyme Finder with us.

Now you can use Excel to help you pursue that song writing career you've always dreamed about 😉

About Ruthie: I’m currently on my third assignment with Synova, Inc. at HP in the Rocky Mountain town of Boise, ID. I’ve loved the power of the Excel since first being introduced to it in the early 1980s. I know many people use it as a great financial tool, but it’s also very handy for manipulating text and updating presentations. I enjoy creating what I call “electronic tools” for solving puzzles, finding answers, and providing easy-to-use interfaces for both my usual work projects and several personal projects.

As of today, I’ve been married to Chris, a wonderful guy, for nine years. My son, Harvé, is quickly approaching graduation from college and we’re looking forward to him starting a new career as a mechanic – I mean “automotive technician”. I like to relax by reading, designing new “eTools”, and spending time with Chris.