**Method 1 – Extracting the Position of a Specified Character in a Text**

In the following dataset, **Column B** contains text values, and **Column C** represents what is to be found in the texts in **Column B**. In **Column D**, we’ll extract the characters’ positions defined in **Column C**.

**Steps:**

- In
**Cell D5**, the formula with the**FIND**function will be:

`=FIND(C5,B5)`

- Press
**Enter. T**he function returns 5, which means the letter**‘T’**lies in the 5th position in the text in**Cell B5**. - Find the next two outputs in
**Cells D6 and D7**.

**Method 2 – Using the FIND Function for Case-Sensitive Character(s)**

In cell **D5**, ‘t’ with lowercase cannot be found, so if you input ‘t’ as a **find_text argument**, you’ll be shown the #VALUE error. In cell** D5**, we’ve defined the **find_text** argument with **‘T’.**

As a result, the function has returned an integer defining the position of** ‘T’** in the text string in **Cell B6**.

**Method 3 – Extracting a Text from the Beginning of a String up to a Certain Position **

The **MID function** returns the characters from the middle of a text string, given a starting position and length. The generic formula of this **MID** function is:

**=MID(text, start_num, num_characters)**

In **Column B**, some texts have spaces inside, and we have to extract the first names or words only.

- Enter the following formula in
**Cell C5**:

`=MID(B5,1,FIND(" ",B5)-1)`

- Press
**Enter.**The word from the beginning of the text will be displayed in**Cell B5**.

In this formula, the **starting_num** for the **MID** function is 1, which means the output text will start with the first character in **Cell B5**. The **FIND** function defines the number of characters or character length up to the first space.

**Method 4 – Drawing Out Text from the End of a String **

The **RIGHT function **returns the specified number of characters from the end of the text string whereas the **LEN function **returns the number of characters in a text string.

There are a few names in Column B, and we have to pull out the last names only in Column C.

- Enter the following formula in
**Cell C5**:

`=RIGHT(B5,LEN(B5)-FIND(" ",B5))`

- Press
**Enter**to get the last name of Andy Robertson.

** How Does the Formula Work?**

➤ **FIND** function returns the position of the first space from the text in **Cell B5** and that is 5.

➤ The **LEN** function returns the total number of characters from the text string in **Cell B5**, which is 14.

➤ The difference between the previous two outputs is the second argument for the **RIGHT** function, which represents the number of characters at the end of a text string.

➤ The **RIGHT** function extracts the last name defined by the number of characters from the end of that text string.

**Method 5 – Finding the Position of a Character Multiple Times **

From **Cell B5**, we’ll find the positions of the 1st and 2nd spaces in the text string. To find the position of the first space,

- Enter the following formula in
**Cell C5**:

`=FIND(" ", B5) `

To find the position of the second space in the text string lying in **Cell B5**,

- Enter the following formula
**Cell D5**:

`=FIND(" ",B5,FIND(" ",B5)+1)`

**Read More:** FIND Function Not Working in Excel

**Method 6 – Returning All Characters Between the 1st & 2nd Occurrences **

**Column B** has several texts with two spaces. For each case, we’ll extract the text between those two spaces in Column C.

- Enter the following formula in
**Cell C5**:

`=MID(B5, FIND(" ",B5)+1, FIND(" ", B5, FIND(" ",B5)+1) - FIND(" ",B5)-1)`

- Press
**Enter**, and the formula will return the extracted data between the two spaces from the text string in**Cell B5**.

** How Does the Formula Work?**

➤ In the second argument **(start_num)** of the MID function, the **FIND** function defines the starting number of the character as the position of the first space.

➤ **FIND(” “, B5, FIND(” “,B5)+1) – FIND(” “,B5)-1**; this part defines the number of characters before the second space from the starting position found in the previous step.

➤ The** MID** function returns the extracted text value based on the described arguments.

**Method 7 – Pulling Out Text within Parenthesis **

In **Column B**, there are a few texts with parenthesis inside. We’ll extract the data lying inside the parentheses only.

- Enter the following formula with
**FIND and MID**function in**Cell C5**:

`=MID(B5,FIND("(",B5)+1, FIND(")",B5)-FIND("(",B5)-1)`

- Press
**Enter,**and you’ll get the text between parentheses at once.

** How Does the Formula Work?**

➤ In the second argument of the **MID** function, the **FIND **function defines the starting number for the **MID** function as the position of the **‘(‘ **and by adding **1** to it.

➤ The third argument of the **MID** function, **FIND(“)”,B5)-FIND(“(“,B5)-1**, defines the number of characters between the opening and closing brackets.

➤ Finally, the **MID** function returns the extracted data or text within the parenthesis.

** Things to Keep in Mind**

If more than one character is included in the **FIND** function, it’ll return the position of the first character only.

**FIND** function does not support wildcard characters as it extracts the position of the defined text only in a numerical value.

As the **FIND** function is case-sensitive, the function will be unable to find the character if you include a text with the wrong case(s) as the find_text argument, thereby returning a **#VALUE** error.

To avoid case sensitivity, use the **SEARCH function** instead of the **FIND** function.

If the **find_text** argument is empty, the function will return **1**.

If the input in the** find_text** argument is found more than once in the selected text string, the function will return the position of the first finding only.

**Download the Practice Workbook**

You can download the Excel workbook to practice.

**<< Go Back to Excel Functions | Learn Excel**