## What Is Substring In Excel?

A Substring in Excel is a string present within the given larger string. But as Excel does not have an inbuilt Substring function,

Textfunctions,LEFT,MID, andRIGHThelp extract the substring of the required length from the given text.Users can use the above functions with

FIND()andSEARCH(). It helps to obtain the substring preceding or succeeding a character or between different occurrences of the given character.

For example, the below table shows a list of original texts and descriptions of the substrings we must extract from the original strings.

Suppose we need to display the extracted substrings in column C. Then, we can apply the **substring in Excel formula** using the Excel **Text** functions in the target cells and achieve the required substrings.

In the above **substring in Excel example**, the requirement is to pull a substring from the left, right, and center in rows 2:4. So, we use the **LEFT()**, **RIGHT()**, and **MID()** to achieve the required substrings in the target cell range C2:C4.

##### Table of contents

###### Key Takeaways

- A substring in Excel is a part of a larger text string. You can use
**Text**functions,**LEFT**,**MID**, or**RIGHT**, to draw a substring of the required length from the original text. - Users can use the Excel
**Text**functions with**SEARCH**and**FIND**functions to achieve the substring before, after, or between two occurrences of a given character. - Using the
**ISNUMBER()**with the**SEARCH**or**FIND**functions, you can find a substring in a given text string. Use the**ISNUMBER()**with the**SEARCH()**for a case-insensitive search. And for a case-sensitive search, apply the**ISNUMBER()**with the**FIND()**.

### Top 3 Ways To Extract Substring In Excel

We can extract a substring in Excel:

- From the left of a text using
**LEFT()**. - From the right of a text using
**RIGHT()**. - From the center of a text using
**MID()**.

Let us see all the methods with an example.

The below image shows three tables containing a list of original texts.

And each table shows the descriptions of what substrings to extract from the specified original texts.

Let us see how to update the above tables using each **Text** function.

#### #1 – Extract Text From The Left Of A String

The **Text** function, **LEFT**, can help us extract the required substring from the left of the original string.

The **LEFT()** syntax is:

where

**text**: The original text containing the substring we require to extract.**num_chars**: The total characters we need to extract from the left of the**text**as the required substring in Excel.

While the first argument is mandatory, the second is optional. Further, please adhere to the points below to ensure we properly apply the **LEFT()** to **search** **substring in Excel** and return it as the output.

- The second argument,
**num_chars**, should be equal to or more than**0**. - For
**num_chars**exceeding the**text**’s length, the**LEFT()**return value will be the given**text**. - If we omit
**num_chars**, Excel will take its default value as**1**.

Let us apply the **LEFT()** to **search substring in Excel** as per the given descriptions and update the target cell range C2:C8.

**Step 1:**Select the target cell C2, enter the below formula, and press**Enter**.

*=LEFT(A2,18)*

Likewise, select each target cell from cell range C3:C8, enter the required **substring in Excel formula**, and press **Enter** to view the result. Refer to the formulas provided in cell range D2:D8, used to update cells in the range C2:C8.

While the first argument in the **LEFT()** is the original text, the second argument shows the total characters we must extract to create the required substring. And when counting the characters, we must also count a space as a character.

We can supply the text directly or the cell reference to the given data as the **text** argument.

In the case of rows 7 and 8, we must supply the date and currency values as text to display the desired substring. In this case, providing cell references to the original texts will lead to the **LEFT() **returning incorrect data and the first digit in the currency, **5**, as the substring.

#### #2 – Get Text From The Right Of A String

The **Text** function, **RIGHT**, can get the required substring from the right of the original text.

The **RIGHT()** syntax is:

where

**text**: The original text containing the substring we require to extract.**num_chars**: The total characters we need to extract from the right of the**text**as the required substring in Excel.

While the first argument is mandatory, the second is optional. Further, adhering to the below points will ensure that we have applied the **RIGHT()** properly.

- The second argument,
**num_chars**, should be equal to or more than**0**. - For
**num_chars**exceeding the given**text**’s length, the**RIGHT()**return value will be the given**text**. - If we omit
**num_chars**, Excel will take its default value as**1**.

Let us use the **RIGHT()** to update the second table in the above **substring in Excel example**.

**Step 1:**Select the target cell C11, enter the below formula, and press**Enter**.

*=RIGHT(A11,22)*

Likewise, select each target cell from cell range C12:C17, enter the required **substring in Excel formula**, and press **Enter** to view the result. Refer to the formulas provided in cells D11:D17, used to update cells in the range C11:C17.

The **RIGHT() **logic remains the same as the **LEFT()**, except the character counting (including spaces) happens from the right end of the given string.

And in row 16, the reason for supplying the date value as the text remains the same as explained in the previous section.

However, unlike the case of row 8, we can provide the cell reference to the currency value as the **text** argument in the **RIGHT()** in cell C17 to obtain the required substring. The reason is that we require the last three digits of the currency, which the **RIGHT()** counts correctly.

#### #3 – Extract Text From The Middle Of A String

If it is required to pull a substring from the text’s center, use the **MID()**.

The **MID()** syntax is:

where

**text**: The original text containing the substring we require to extract.**start_num**: It is the position of the first character required to extract, with the counting starting from the left of the given text.**num_chars**: The total characters we need to extract from the mid of the**text**, starting from the specified**start_num**, as the required substring in Excel.

All three arguments in the **MID()** are mandatory. And adhering to the below points ensures we use the **MID()** correctly.

- For a
**start_num**argument value exceeding the original text’s length, the**MID()**returns an empty text. - Suppose the
**start_num**argument value is less than the original text’s length, but the total of**start_num**and**num_chars**values exceeds the given text’s length. Then, the**MID()**return value is the characters till the original text end. - For a
**start_num**value less than one or a negative**num_chars**, the**MID()**return value is the**#VALUE!**error.

Let us use the **MID()** to update the third table in the above** example**.

**Step 1:**Select the target cell C20, enter the below formula, and press**Enter**.

*=MID(A20,20,2)*

Similarly, select each target cell from cells C21:C26, enter the formula for extracting the required substring, and press **Enter** to view the result. Refer to the formulas provided in cells D20:D26, used to update cells C20:C26.

The **MID()** accepts the original string as the **text** argument. The second argument value indicates the position from where we need to start counting the characters (including spaces) to form the required substring. And the third argument denotes the total characters we must count to obtain the substring as per the given description.

The reference to the cell containing the original text as the **text** argument will work in the **MID()**. But the reason for supplying the date value as text in the cell C25 **MID()** is the same as explained previously.

On the other hand, when counting the second and third-last digits in the amount given in cell A26, the counting starts from the number **5** (ignoring the ‘**$**’ symbol). And excluding the comma makes the required **start_num** value **3**.

Alternatively, we can apply the **LEFT()**, **RIGHT()**, and **MID()** from the **Formulas** tab. And for that, we must select the target cell, go to **Formulas** → **TEXT**, and pick the required function.

And once we click on the required function, the **Function Arguments** window will open, where we must enter the applicable argument values and click **OK** to obtain the substring.

For example, let us update cell C26 with the **MID()** from the **Formulas** tab.

**Step 1:**Select the target cell C26, and click**Formulas**→**Text**→**MID**to open the**Function Arguments**window.

**Step 2:**Enter the**MID()**arguments in the respective fields in the**Function Arguments**window. And click**OK**to close the window and view the**MID()**output as the required substring in cell C26.

Please note that the functions **LEFT**, **MID**, and **RIGHT** return text, even when extracting numbers.

### Examples

Check out the following illustrations to learn how to use substring in Excel.

Using the Excel **Text** functions with **SEARCH()** and **FIND() excel function**, we can create Excel formulas to achieve the required substrings. While the **SEARCH()** gives a case-insensitive formula, the **FIND()** helps create case-sensitive expressions.

#### Example #1 – Get Text Before A Specific Character

The below table contains a list of text strings.

Suppose the requirement is to display the substring occurring before the ‘**_**’ in each original text in column B. Then, as we need a substring from the left to the underscore, we shall apply the **LEFT()** with the **SEARCH()** in the target cells.

**Step 1:**Select the target cell B2, enter the following expression for obtaining the required substring in Excel, and press**Enter**.

*=LEFT(A2,SEARCH(“_”,A2)-1)*

**Step 2:**Using the fill handle, implement the above formula in cell range B3:B4.

Let us see the cell B4 expression to check how the formula works. First, the **SEARCH()** searches for the ‘**_**’ position in cell A4 text (including spaces) and returns the value of **23**. Next, the **num_chars** expression in the **LEFT()** deducts one from the **SEARCH()** output to return the value of **22**. Finally, the **LEFT()** returns a 22-character (including spaces) substring from the left of the original string, **Kevin Reed (Team Lead)**.

#### Example #2 – Extract Text After A Certain Character

The table below contains a list of text strings.

Suppose the requirement is to obtain the substring after the ‘**:**’ in the text strings specified in each row and display the results in column C. It implies we require the substrings from the right of the original texts. Then, applying the **RIGHT()** with the **LEN excel function** and **SEARCH** functions in the target cells will get us the required substrings from the right of the given texts till ‘**:**’.

**Step 1:**Select the target cell C2, enter the following expression for obtaining the required substring in Excel, and press**Enter**.

*=RIGHT(B2,LEN(B2)-SEARCH(“:”,B2))*

The **LEN()** returns the length of the cell B2 text, **16**. And the **SEARCH()** returns the position of ‘**:**’ in the cell B2 text, **6**. So, the **num_chars** expression finds the difference between the **LEN()** and **SEARCH()**, **10**. And finally, the **RIGHT()** returns the 10-character substring from the right of the original text, **Wisconsin**, with a space character in front.

**Step 2:**Use the fill handle to enter the formula in cell range C3:C4.

However, the original texts contain a space after ‘**:**‘. And so, the resulting substrings in column C contain space before the texts.

So, we can use the **REPLACE() excel function**, which helps **replace a substring in Excel**, to acquire the required substrings without the additional space characters in the target cells.

**Step 1:**Introduce a new target column D, select the target cell D2, enter the below formula, and press**Enter**.

*=REPLACE(C2,1,10,RIGHT(C2,9))*

**Step 2:**Implement the above formula in cell range D3:D4.

Let us check the cell D4 expression to see how the formula works.

First, the **RIGHT()** returns an 11-character substring of the cell C4 text, **Pilgrim Way**.

Then, the **REPLACE()**, which helps **replace a substring in Excel**, accepts the cell C4 data as the **old_text** argument and the **RIGHT()** output as the **new_text **argument. And it takes **1 **and **12 **as **start_num** and **num_chars**. It implies that counting the cell C4 text characters starts from “ “ in the front till the last character, ‘**y**’. Finally, the **REPLACE()** replaces the selected characters with the new text the **RIGHT()** returned.

#### Example #3 – Pull Text Between Two Occurrences Of A Character

The below table contains two text strings.

Suppose the requirement is to draw substrings from the original texts between the mentioned ‘**;**’ positions in each row and display them in column D.

As we require a substring from the mid of the given text, we can apply the **MID() **with the **FIND** and **SUBSTITUTE excel **functions in the target cells to obtain the required substrings.

**Step 1:**Select the target cell D2, enter the below formula to achieve the desired substring, and press**Enter**.

*=MID(B2,FIND(CHAR(96),SUBSTITUTE(B2,”;”,CHAR(96),1))+1,FIND(CHAR(96), SUBSTITUTE(B2,”;”,CHAR(96),2))-FIND(CHAR(96),SUBSTITUTE(B2,”;”,CHAR(96),1))-1)*

**Step 2:**Select the target cell D3, enter the below formula to acquire the desired substring, and press**Enter**.

*=MID(B3,FIND(CHAR(96),SUBSTITUTE(B3,”;”,CHAR(96),2))+1,FIND(CHAR(96), SUBSTITUTE(B3,”;”,CHAR(96),4))-FIND(CHAR(96),SUBSTITUTE(B3,”;”,CHAR(96),2))-1)*

Let us see the cell D3 expression to understand how the formula works.

The first **FIND()** in the **start_num** argument helps find the first occurrence of the specified character to identify the starting point for counting the desired substring characters. And the second and third **FIND()** in the **num_chars** expression help determine the total characters to count till the next specified occurrence of the given character.

The **CHAR()** returns the character ‘**`**’ (Grave accent). Next, the first **SUBSTITUTE()** substitutes the second occurrence of ‘**;**’ in the cell B3 text with ‘**`**’. Then, the first **FIND()** finds the position of ‘**`**’ in the cell B3 text and returns the position as **17**. So, the **start_num** argument expression adds the value **1** to the **FIND()** output to return the value of **18**.

Similarly, the second and third **FIND** functions form the **num_chars** expression in the **MID()**. And while the second **FIND()** returns a value of **31**, the third returns **17**. So, the **num_chars** expression finds the difference between the second and third **FIND** function outputs and then deducts a one from the difference to return a value of **13**.

Finally, the **MID()** counts 13 characters from the 18^{th} position in the given text to return the required substring between the second and fourth ‘**;**’ as **Banana;Orange**.

#### Example #4 – How To Find Substring In Excel (Partial Match)

We can find a substring using the **ISNUMBER() excel function **with the **SEARCH** or **FIND** functions, depending on whether we want to perform a case-insensitive or sensitive search.

For example, the below table contains three text strings.

Suppose the requirement is to find the substring “**12GH**” in the given original texts and display the output in columns B and C. Then, here is how we can apply the **ISNUMBER()** with the **SEARCH** and **FIND** functions within the **IF()** and perform case-insensitive and sensitive searches in the target columns.

**Step 1:**Select the target cell B2, enter the below formula, and press**Enter**.

*=IF(ISNUMBER(SEARCH(“12GH”,A2)),”Present”,”Not Present”)*

**Step 2:**Select the target cell C2, enter the below formula, and press**Enter**.

*=IF(ISNUMBER(FIND(“12GH”,A2)),”Present”,”Not Present”)*

**Step 3:**Select cells in the range B2:C2 and use the fill handle to apply the formulas in cell range B3:C4.

The **SEARCH** and **FIND** functions perform a case-insensitive and sensitive search of the specified substring in each original text. If the search result returns a number, the specified substring’s first character’s position in the original text, the **ISNUMBER()** returns **TRUE** as the output. And thus, the **IF()** holds in the target cell, and the output is the **TRUE** value, **Present**.

All the target cells show the substring is present in the given texts, except cell C3. The reason is that the **FIND()** performs a case-sensitive match. And here, the substring case in the original text does not match the case of the string mentioned in the **FIND()**. And thus, the **FIND()** does not return a number, leading to the **ISNUMBER()** output being **FALSE**. As the **IF** condition is **FALSE**, its return value is **Not Present** in the target cell C3.

### Important Things To Note

- When using Excel
**Text**functions,**LEFT**or**RIGHT**, the**num_chars**must be equal to or more than**0**. And if**num_chars**is more than the given**text**’s length, the functions return the**text**. Also, for an omitted**num_chars**, Excel takes its default value of**1**. - If the
**MID()**argument**start_num**exceeds the given text’s length, the function returns an empty text as the substring in Excel. But suppose the**start_num**is less than the given text’s length, but the sum of**start_num**and**num_chars**exceeds the text string length. - Ensure the
**start_num**is not below one or**num_chars**is not negative to avoid the**MID()**returning the**#VALUE!**error.

### Frequently Asked Questions (FAQs)

**1. Is there a Substring function in Excel?**

There is no Substring function in Excel. However, you can apply the Excel **Text** functions, **LEFT**, **MID**, or** RIGHT**, to draw the required substring from the original text.

You can also use the Excel **Text** functions with the **FIND()** and **SEARCH() **to achieve the substring before, after, or between two occurrences of the specified character.

**2. How to extract a substring before the first space in a text string?**

You can extract a substring before the first space in a text string using the **LEFT()** with the **SEARCH()**.

Let us see the steps with an example.

The below table shows a text string in cell A2.

Here is how you can extract the substring before the first space in the specified text string and display the result in cell B2.**• Step 1: **Select the target cell B2, enter the below formula, and press **Enter**.*=LEFT(A2,SEARCH(” “,A2)-1)*

First, the **SEARCH()** searches for “ “ in the given text, **Christopher Columbus**, and returns the position of its first occurrence, **12**. The **num_chars** expression in the **LEFT()** deducts one from the **SEARCH()** to return the value of **11**. And thus, the **LEFT()** returns an 11-character substring from the left of the given text, **Christopher**.

**3. Why is the substring in Excel not working?**

The substring in Excel is not working, perhaps because of the following reasons:**• **The argument **num_chars** in the **LEFT **or **RIGHT** functions is negative or more than the given **text**’s length.**• **The argument **num_chars** in the **MID()** is negative.**• **The **start_num** argument in the **MID() **is below one or more than the given text’s length.**• **The **start_num** is less than the given text’s length, but the total of **start_num** and **num_chars** exceeds the given text’s length.

### Download Template

This article must be helpful to understand the **Substring In Excel**, with its formula and examples. You can download the template here to use it instantly.

### Recommended Articles

This has been a guide to Substring In Excel. Here we learn top 3 ways to extract text from different parts of string, examples & downloadable excel template. You can learn more from the following articles –

## Leave a Reply