How to Extract Parts of a Text String in Excel Formula

A text string is a sequence of characters that can be entered in a cell or a formula. For example, “Hello World” is a text string. Sometimes, you may need to extract a part of a text string for various purposes, such as finding a specific word, splitting a name, or extracting a code. Excel provides several functions to help you extract parts of a text string, such as LEFT, RIGHT, MID, FIND, SEARCH, and more. These functions can be combined with other functions and operators to create complex formulas that can handle different scenarios.

The general procedure to extract parts of a text string in Excel formula is as follows:

1. Identify the source text string and the part that you want to extract. For example, you may want to extract the first name from a full name, or the domain name from an email address.
2. Choose the appropriate function(s) based on the position and length of the part that you want to extract. For example, you can use the LEFT function to extract a fixed number of characters from the left of a text string, or the MID function to extract a variable number of characters from the middle of a text string.
3. Enter the formula in a cell and press Enter. You can also copy and paste the formula to other cells if needed.
4. Check the result and make any adjustments if necessary. For example, you may need to trim any extra spaces or convert the text to a different case.

Explanation

In this section, we will explain how to use some of the most common functions to extract parts of a text string in Excel formula, and provide some examples to illustrate their usage.

LEFT Function

The LEFT function returns a specified number of characters from the left of a text string. The syntax of the LEFT function is:

=LEFT(text, [num_chars])

Where text is the text string that you want to extract from, and num_chars is the number of characters that you want to extract. If num_chars is omitted, it defaults to 1.

For example, suppose you have a list of product codes in column A, and you want to extract the first two characters from each code. You can use the following formula in column B:

=LEFT(A2, 2)

This formula returns the first two characters from the text in A2. You can copy and paste the formula to other cells in column B to get the results for the rest of the codes.

RIGHT Function

The RIGHT function returns a specified number of characters from the right of a text string. The syntax of the RIGHT function is:

=RIGHT(text, [num_chars])

Where text is the text string that you want to extract from, and num_chars is the number of characters that you want to extract. If num_chars is omitted, it defaults to 1.

For example, suppose you have a list of phone numbers in column A, and you want to extract the last four digits from each number. You can use the following formula in column B:

=RIGHT(A2, 4)

This formula returns the last four characters from the text in A2. You can copy and paste the formula to other cells in column B to get the results for the rest of the numbers.

MID Function

The MID function returns a specified number of characters from the middle of a text string, starting at a position that you specify. The syntax of the MID function is:

=MID(text, start_num, num_chars)

Where text is the text string that you want to extract from, start_num is the position of the first character that you want to extract, and num_chars is the number of characters that you want to extract.

For example, suppose you have a list of email addresses in column A, and you want to extract the user name from each address. You can use the following formula in column B:

=MID(A2, 1, FIND(“@”, A2) – 1)

This formula returns the characters from the text in A2, starting from the first character, and ending at the position before the “@” character. The FIND function returns the position of the “@” character in the text. You can copy and paste the formula to other cells in column B to get the results for the rest of the addresses.

FIND and SEARCH Functions

The FIND and SEARCH functions return the position of a specific character or substring within a text string. The syntax of the FIND and SEARCH functions are:

=FIND(find_text, within_text, [start_num])

=SEARCH(find_text, within_text, [start_num])

Where find_text is the character or substring that you want to find, within_text is the text string that you want to search within, and start_num is the position from which you want to start the search. If start_num is omitted, it defaults to 1.

The difference between the FIND and SEARCH functions is that the FIND function is case-sensitive, while the SEARCH function is not. Also, the SEARCH function allows using wildcard characters, such as “?” and “*”, to match any single character or any sequence of characters, respectively.

For example, suppose you have a list of names in column A, and you want to find the position of the first space character in each name. You can use either the FIND or the SEARCH function in column B, as follows:

=FIND(” “, A2)

=SEARCH(” “, A2)

Both formulas return the same result, which is the position of the first space character in the text in A2. You can copy and paste the formula to other cells in column B to get the results for the rest of the names.

Example

To demonstrate how to use the above functions to extract parts of a text string in Excel formula, let’s consider the following scenario and example.

Scenario: You have a list of product names in column A, and you want to extract the brand name, product type, and model number from each name. The product names have the following format: brand name – product type (model number).

Example: Here is a sample list of product names and the desired output.

To extract the brand name from the product name, we can use the LEFT function with the FIND function, as follows:

=LEFT(A2, FIND(“-”, A2) – 2)

This formula returns the characters from the left of the text in A2, until the position before the “-” character, minus two spaces.

To extract the product type from the product name, we can use the MID function with the FIND function, as follows:

=MID(A2, FIND(“-”, A2) + 2, FIND(“(”, A2) – FIND(“-”, A2) – 3)

This formula returns the characters from the middle of the text in A2, starting from the position after the “-” character, plus two spaces, and ending at the position before the “(” character, minus three spaces.

To extract the model number from the product name, we can use the MID function with the FIND function, as follows:

=MID(A2, FIND(“(”, A2) + 1, FIND(“)”, A2) – FIND(“(”, A2) – 1)

This formula returns the characters from the middle of the text in A2, starting from the position after the “(” character, plus one space, and ending at the position before the “)” character, minus one space.

The final result is shown below.