# How to use Excel's Find Function

October 25, 2017

When working with text, sometimes you need to look for certain characters in
order to parse out pieces of a string. With Excel’s `FIND()`

function, you can
get the position of the character (or characters) that you’re looking for.

## Definition and Syntax

The `FIND()`

function searches for a specific string within some text and
returns the position of where it found the string.

Note that the starting index of the text being searched on is 1.

`FIND(find_text, within_text [, start_num])`

Parameter | Definition |
---|---|

`find_text` |
This is the text you will be searching for. |

`within_text` |
This is the text that you will be searching in. |

`start_num` |
Optional. This is the starting position within the text where you want to begin searching the `find_text` . |

## Other Notes

`FIND()`

is case sensitive. This means that if you look for “sally” (lower case) it will return an error (#VALUE!).- If
`find_text`

is not found within the string, #VALUE! is returned. - If
`find_text`

is blank or missing, like`=FIND(,A1)`

, it will return`1`

(the first position of the string). - If
`start_num`

is outside the index range of the string, #VALUE! is returned. For example if we have the string “Hello” where the indexes of the string are 12345 (1=H, 2=e, 3=l, etc.) and we put a number in start_num that is not within those numbers, #VALUE! is returned. - You can’t use wildcards with the
`FIND()`

function. For that, you would want to use the`SEARCH()`

function.

## Examples

For the following examples we’ll use this text:

`Sally sells sea shells by the sea shore. The shells Sally sells are surely from the sea.`

### Find the First Word in a Sentence

```
=FIND(" ",A1)
=6
```

The first space found in the text is at position `6`

. We can then combine this
info with the
LEFT() function
to get the first word of the sentence.

```
=LEFT(A1,FIND(" ",A1)-1)
=Sally
```

Note that the `-1`

is in the formula to remove the trailing space, so instead of
returning `"Sally "`

it returns `"Sally"`

.

### Find the Second Word in a Sentence

To get the second word in a sentence, we’ll have to use the MID() function in combination with two FIND() functions: one to get the first space and another to get the second space.

Before we begin, remember that the syntax for MID() is:

`=MID(text, start_num, num_chars)`

The idea here is to get the start_num where the second word begins. Then we need the amount of characters the second word is to put into the num_chars parameter.

```
Text: Sally sells ...
Index:123456789abc... (where a=10, b=11, and c=12)
Goal: Start at index 7 (start_num) and get 5 characters (num_chars)
=MID(A1, FIND(" ",A1)+1, FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1)
-- -------------- ------------------------------------------
text start_num num_chars
Note: Each underlined section represents what is being evaluated
1) =MID(A1, FIND(" ",A1)+1, FIND(" ",A1, FIND(" ",A1)+1) -FIND(" ",A1)-1)
------------
2) =MID(A1, 6+1, FIND(" ",A1, FIND(" ",A1)+1) -FIND(" ",A1)-1)
---
3) =MID(A1, 7, FIND(" ",A1, FIND(" ",A1)+1)) -FIND(" ",A1)-1)
- ---------------
4) =MID(A1, 7, FIND(" ",A1, 7) -FIND(" ",A1)-1)
------------ --
5) =MID(A1, 7, 12 -FIND(" ",A1)-1)
-- ------------
6) =MID(A1, 7, 12 -6-1)
-- ----
7) =MID(A1,7,5)
-----------
8)=sells
```

#### Getting the Second Word - Breakdown

I know, I know, this is pretty confusing. Let’s break this down into the steps to provide a more clear explanation.

- The whole formula. We also show the parts that we are looking to get for the
`MID()`

function. - We start with getting the start_num for
`MID()`

. The first`FIND()`

function is looking for the first space. - The first space evaluates to
`6`

. - The
`start_num`

evaluates to`7`

, which is the “s” in “sells”. Now let’s start evaluating the`num_chars`

parameter for the`MID()`

function. We’ll start by evaluating getting the first space`+1`

. - As before, it evaluates to
`7`

. Now we are looking for the second space’s position. Note that we are looking for a space after the first space was found at position`6`

(we have to`+1`

position`6`

to ignore the same space for the`FIND`

function). - We get
`12`

for the position of the second space. - Now we want to get the position of the first space again because we’re trying
to get the number of characters for the second word (which is
`5`

characters). - Again, getting the first space is at position
`6`

. - Evaluating
`12-6-1 = 5`

. - We finally get the second word “sells” without any beginning or trailing spaces.

As you can imagine, getting the 3rd or 4th word can get a little hairy. If you need to get the first N words from a string, check out this post.