In this guide, I will show you how to extract texts from within a cell. I will use a combination of Text (LEFT, RIGHT, and MID) SUBSTITUTEand SEARCH functions to get substrings from before, end or after a specific character of a string.
How to Get Substring in Excel: Start, Middle or End of a String
You can get a substring from the end, middle or beginning of a string. The following sections covers how to do this.
How to Get Excel Substring from the Start of a String
You can get substrings from the left of a string in Excel using the LEFT Function. Below is the Syntax of the LEFT Function:
Text is a (required parameter): This is the text string you want to extract the Excel substring from.Num_chars parameter is Optional. Characters count (subsrting) you want the LEFT function to extract from the string. To get the first 4 characters from this string, 7OV1-BK8P-QD9Y use the following formula: The formula above assumes that the string is in cell A6. Here is the result. You can also specify the string directly in the formula as shown below It produces the same result as shown below:
How to Get Excel Substring from the Right of a String (RIGHT)
The RIGHT function is used to get substring from the right of a string. Here is the Syntax: Text (Required parameter): The text string containing the characters you want to extract.Num_chars (Optional parameter): Characters you want RIGHT to extract right of the string. Continuing from the previous example, to extract the first 5 characters counting from the right of this string, 7OV1-BK8P-QD9Y use the formula: The result in Excel:
How to Get Excel Substring Within a String (MID)
There are circumstances you may need to extract excel substring from within a string. You will need the MID function for this. The Syntax of the MID function is: Text (Required): The Excel string with the characters you want to extract.Start_num (Required): The position of the first character you want to extract in text. The first character in text has start_num 1, and so on.Num_chars (Required): Specifies the number of characters you want MID to return from text. As an example, to extract BK8P substring from from 7OV1-BK8P-QD9Y, use this: This tells Excel to extract 4 characters from cell A2, starting from the 6th character. See the result in Excel.
How to Get Substring in Excel: Before or After a Specific Character
So far, this guide have covered how to extract excel substing from the beginning, within and end of a string. In this section, I will discuss how to extract a substring after a particular character.
How to Get Excel Substring Before a Specific Character
In this example, I will show you how to get all characters before the first hyphen (counting from left) in 7OV1–BK8P-QD9Y. There are 2 steps to complete this task: First, I will show how to use SEARCH to determine the position of the character. Here is the Syntax of the SEARCH function: find_text (Required): This is the text you want to find. In our ongoing example, this will be the hyphen, “-“. within_text (Required): The text in which you want to search for the value of the find_text argument. This is usually the cell number containing the text. For example, the cell containing 7OV1–BK8P-QD9Y. start_num (Optional): The character number in the within_text argument at which you want to start searching. To get the position of the first hyphen, “-” in 7OV1-BK8P-QD9Y, use the formula: The result is 5 as shown in the result below: The next step is to use the LEFT function to extract all substrings before “-“. Below is the Syntax: text: is a parameter of FIND. This is the original string (usually the cell containing the text)find_text: is a parameter of SEARCH. This specifies the character you want to extract all characters before it. within_text: Still a parameter of SEARCH. Same as text parameter in this instance. 1: The value returned by SEARCH is usually over by 1. Subtracting 1 corrects the result. And now the formula for our example: To make the seemingly complex formula easy to understand, I will break it down into the 2 parts. Then put it together again. Part 1: Here, LEFT will extract num_chars from the text in cell A2. num_chars is the number of characters from the left of the text to extract. Part 2 This tells SEARCH to find the position of “-” character from the text in cell A2. When we put it together, we replace num_chars in LEFT function with (SEARCH(“-“, A2))-1. And here is the combined formula: And the result in Excel:
How to Get Excel Substring After a Specific Character
In this section, I will demonstrate how to extract Excel substring after a specific character. I want to extract all characters after the first “-” in 7OV1-BK8P-QD9Y. The formula is similar to the example in the last section. The difference is that in this section, I will replace LEFT with RIGHT function. I will also use the within_text parameter in the SEARCH function. Here is the general syntax for extracting Excel substring after a specific character in a text. text: Is a parameter of the RIGHT function. This is the cell containing the string you wish to extract Excel substring from. find_text: SEARCH parameter. Specifies the character you wish to extract characters from. text: The second text is a parameter of SEARCH function. This is the cell containing the string you are extracting from. within_text: This is used to tell SEARCH from what character to search for find_text. In our example within_text is “-“. And here is the formula to extract all characters after the first “-” in 7OV1-BK8P-QD9Y A2 is the cell with the text. “-” is the character we are extracting after and 6 is the character we are SEARCHING for “-“, from the right of the original string. Below is the result in excel.
Count How Many Times a Substring Appears in Excel
In the last section of this guide, I will show how to count how many times a substring appears in a text. The Syntax to count the number of times a specific character appears in string is: text: is the main string we want to count fromcount_text: is the character we want to count. For example, to count how many times “-” appears in 7OV1-BK8P-QD9Y, use the formula: Breaking it down: Let’s start with the formula: This removes the character, “-” we want to count from the source string, 7OV1-BK8P-QD9Y. The result is 7OV1BK8PQD9Y as shown below: Next, LEN function counts the characters left after the text we wish to count has been removed from the original text. Here is the formula Then, we count the number of characters in the original string, with this formula: The original text has 14 characters as shown below: Finally, subtract LEN(SUBSTITUTE(A2,”-“,””)) from LEN(A2) And the final result is 2. See the result in Excel below.
Conclusion
I hope this guide simplified Excel substring for you! I also hope you found the Itechguide helpful. If you did, please share your experience using Excel substring at Itechguides Community Forum. You could also ask a question or leave a comment on our forum at Itechguides Community Forum. Our community will attend to you as soon as possible.
Other Helpful Guides
Absolute Reference vs Relative Reference Excel: Quick Guide How to Make a Line Graph in Excel How to Convert Text to Number in Excel in 4 Easy Ways
Additional Resources and References
Text functions (reference) Left, Mid, and Right functions in PowerApps Extract variable length substring from string Itechguides Community Forum.
