Excel Exercise

Medium

4 minutes

Does Excel contain a function that checks if a cell contains text? No. Should it? Absolutely. Like we’ve done before, with other functions that don’t exist (IFNOTERROR, ISNOTERROR) we’re going to create the Excel Contains function ourselves. It’s not that hard!

Excel CONTAINS function

So we want a formula that returns TRUE if a cell contains a specific text, and FALSE if it doesn’t.

For example, we may have a list of names with Mr (for Mister) and Ms (for Miss) in front of the name.

To determine the gender, we could check if the text “Mr” is used in a cell to determine if the person is male. Like this:

NameContains “Mr”
Mr. James GordonTRUE
Ms. Anna YoungFALSE
Ms. Annie HensleyFALSE
Mr. Tyler PatelTRUE

Alright, so how do we make this function? Well if you just want the answer, you can click the “Show answer” button in the bottom right of the exercise below.

But if you want to try it for yourself, here’s a hint: you need to combine two functions. One of them is called SEARCH, which works like this:

=SEARCH(Find text, Within text)

It returns the index at which the text is found. If you need more hints, you can keep reading below the exercise.

Great job! You’ve made your own Excel function!

Another hint

Scroll down for the second hint.

Hint #2

So the other function that you want to use is ISNUMBER. Here’s the syntax for Isnumber:

=ISNUMBER(Value)

This will return if the given value is a number (TRUE) or not (FALSE). Think about how you can combine this with SEARCH to get the CONTAINS function.

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"36c04":{"name":"Main Accent","parent":-1},"3a8fd":{"name":"Accent Light","parent":"36c04","lock":{"saturation":1,"lightness":1}}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"36c04":{"val":"rgb(255, 204, 102)","hsl":{"h":40,"s":1,"l":0.7}},"3a8fd":{"val":"rgb(241, 241, 241)","hsl_parent_dependency":{"h":0,"s":0,"l":0.94}}},"gradients":[]},"original":{"colors":{"36c04":{"val":"rgb(255, 204, 102)","hsl":{"h":40,"s":1,"l":0.7}},"3a8fd":{"val":"rgb(241, 241, 241)","hsl_parent_dependency":{"h":0,"s":0,"l":0.94}}},"gradients":[]}}]}__CONFIG_colors_palette__

​Get Good at Excel on Autopilot

Sign up to our newsletter and receive ​Excel articles, tips and tricks delivered straight to your inbox. All you have to do is read them! ​Plus, to get you started, you'll receive a PDF with 200+ Excel shortcuts.

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"2dd0d":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"2dd0d":{"val":"rgb(45, 164, 92)","hsl":{"h":143,"s":0.57,"l":0.41}}},"gradients":[]},"original":{"colors":{"2dd0d":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Join List
>