6. Search for Text in a Dataset

 
Subtitles Enabled
Replay Lesson

Next lesson: Split Address Field into Multiple Columns

Watch next lesson
100%

Overview

To search for text in our dataset, we can use the Find and Replace dialog box or alternatively, we can use the FIND function in our formulas. I'll compare both options in this lesson.

Lesson Notes

New functions

FIND: Identifies the position of a specific characters
ISNUM: Returns TRUE if a cell is a numerical value

Keyboard shortcuts

CTRL + F: Open Find and Replace dialog box
ALT + I, C: Insert column

Transcript

Excel offers a number of ways to search for substrings of text within cells.

Perhaps the easiest way to do this is to use the "find and replace" dialogue box.

To open the dialogue box, we can use the shortcut "Ctrl F", and then we can type in the text that we'd like to search for.

Let's say I'd like to perform a search on all addresses that are based in California.

Well then I'll type "CA", which is the state code, and then "Alt + I" will find all of these instances.

I'll then move the dialogue box up towards the top of the page and drag it down so you can see some of the results.

Unfortunately, this search method has a number of limitations.

First, it has identified some instances of "CA" that are outside the address column, such as cell I 38, which is "Scarborough".

To fix this, we'll close the "find and replace" dialogue box, and then we'll select our address column, and then we'll re-search with "Alt + I".

And this time when we look at the search results you can see that all of the results are limited to the selected address column "N".

However, there's a bigger limitation with this search method, and that is that we can't really perform any action with these results apart from viewing them individually.

So if we want to search for a text string and perform an action on the results, we'll need a different approach.

As you might've guessed, this approach will utilize the Find function we've used in previous lessons.

Let's say for example, our company would like to contact every California based user in the next week with a new offer.

We need to create a new column that will identify each user as either from California or not from California.

To do this, we'll use the Find function to search for "CA" within a string and then return true if it exists, and false if it does not.

I'll start by creating a new column with "Alt + IC" and I'll simply call it "Based in California".

And now I'll use the Find function to see if "CA" exists within our string.

Then I'll close the bracket and press Enter.

And when I autofill for the remaining cells, you can see that the column returns a number if "CA" does exist, and it returns an error when the address is another state.

All we need to do now is to find a function that will check if our result is a number or not.

Thankfully, Excel gives us a very useful function to do this, called "isnumber".

So I'll jump back into the formula by pressing F2, and at the very start I'll write "isnumber", and open a bracket.

Then I'll move to the end of our cell and close the bracket, and then press Enter to finish.

When I autofill for the remaining cells, you can see that we now have a "true" result for every address within California, and a "false" result for every other state.

Exactly what we want.

If you'd like to return a value other than true or false, then you can simply put an If function before the Isnumber function, and then select the output you'd like the cell to show.

For example, if you wanted to return the full address if the address was in California, and an empty string if it was outside, then we can jump back into the cell with F2 and put an If function at the very start.

Then we'll move to the end of the cell, write a comma, and then select the address field that we'd like to include if the value is true, and then an empty string if the value is false.

I'll then close the bracket and press Enter.

We can again autofill for the remaining cells.

And now you can see, we show the addresses if it's based in California, and then an empty cell if it's outside.

While searching within strings has a lot of use cases, we're better off splitting address fields into different columns because the state column and post code column are great for sorting, filtering, and use in pivot tables.

In the next lesson, I'll show you exactly how to do this.