How to Extract Substrings from Excel Cell by using TEXT Function?

How to Extract a Substring in Excel Cell by using TEXT Function?

Extract Substrings from Excel cells using the TEXT function to unlock a new realm of data manipulation and analysis, making your spreadsheets more dynamic and informative. This technique is a boon for data analysts, marketers, and anyone who deals with large datasets, enabling them to isolate specific parts of a string for detailed examination or reporting. Whether you’re parsing complex codes, dissecting standardized formats, or simply organizing textual data into more usable formats, this guide will lead you through the nuances of using the TEXT function to efficiently extract substrings, enhancing the versatility and depth of your data analysis endeavors in Excel.

The TEXT Functions in Excel are quite powerful. Using these functions, you can perform various text slice and dice operations. The extraction of a substring in Excel is one of the frequent chores for those working with text data (i.e., getting part of the text from a cell).

Regrettably, Microsoft Excel lacks a simple substring function that may execute this. Although text formulas and a few more built-in Excel functions may continue to be used to do this,

Before we get started, let’s take a quick look at some of the TEXT Functions we’ll be using in this tutorial

This Tutorial Covers:

What is Excel TEXT Functions
- Extract substring from the start the of string (LEFT)
- Get substring from the end of the string (RIGHT)
- Extract text from the middle of the string (MID)
Extract substrings before or after a given character
- How to extract text before a specific character
- How to extract text after a specific character
How to extract text between two instances of a character
How to find substring in Excel

1. What is Excel TEXT Function?

It would be simple to extract a substring from the source text in Excel because of its variety of text operations. The Excel Text functions that will be used in this tutorial are listed below:

RIGHT function: Removes the provided number of characters from the text string’s right side.

LEFT function: Removes the predetermined number of characters from the text string’s left side.

MID function: Use the MID function to extract a text string’s starting position and the defined starting number of characters.

FIND function: Locates the beginning of the given text within the text string.

LEN function: Gives the text string’s character count back (length of the substring).

Extract substring from the start of the string (LEFT):

Using the Excel LEFT function, you can extract text from the left of a string:

=LEFT(text, [num_chars])

Where text is the cell’s address where the source string is located, and num chars are the desired characters from the left to extract.

Use the formula of LEFT, for instance, to extract the first six characters from the start of a text string:

=LEFT(A2,6)

Get substring from the end of the string (RIGHT):

Use the Excel RIGHT function to extract text from excel cell:

=RIGHT(text, [num_chars])

For instance, use the following substring function in Excel to extract the final three characters from a string:

=RIGHT(A2,3)

Extract text from the middle of the string in Excel (MID):

MID is the function to use if you want to extract text from cell excel that begins in the center of a string at the place you provide.

MID has a slightly different syntax from the other two Text functions:

=MID(text, start_num, num_chars)

You specify start_num in addition to text (the initial text string) and num_chars (the number of characters to extract) (the starting point).

You apply the following formula in our sample data set to extract four characters from the center of a string that starts with character 8.

=MID(A2,8,4)

Tip: Even when you are extracting a number from a text string, the Right, Left, and Mid formulas always produce text as their result. Wrap your formula in the VALUE function as shown if you wish to work with the result as a number:

=VALUE(MID(A2,8,4))

2. Extract a substring in Excel before or after a given character

The Left, Right, and Mid functions work well with uniform strings, as evidenced by the examples above. More complicated manipulations are required when working with text strings that are variable in length.

Please take note that we will always use the case-insensitive SEARCH function to determine a character’s position in all of the instances below. Use the FIND function if you need a formula that takes case into account.

How to extract text before a specific character?

Two steps are required to obtain a substring that comes before a specific character: first, you must locate the character you are interested in, and then you must retrieve all characters that come before it. In order to avoid including the character itself in the output, use the SEARCH function to locate the character’s position and then deduct 1 from the result. The received number is then immediately sent to the LEFT function’s num_chars argument:

=LEFT(cell, SEARCH(“char”, cell)-1)

We will, for instance, derive usernames from Email Ids. The @ symbol between the username and the domain name in this instance constitutes the pattern, and we will make use of it to obtain the usernames.

The method to determine a username is as follows:

=LEFT(A2,SEARCH(“@”,A2)-1)

The above formula locates the location of the @ symbol in the id and uses the LEFT function to extract the username. The FIND function is used for this, and it returns the position of the @.

How to extract text after a specific character?

To determine the domain name, follow the same argument as in the case above. Here, there is a small variation in that we must remove the characters from the text string’s right side.

This can be done using the following formula:

=RIGHT(cell,LEN(cell)-SEARCH(“char”, cell))

To ensure we are obtaining the right string, we modify the reasoning in the formula above while still using it.

=RIGHT(A2,LEN(A2)-SEARCH(“@”,A2))

The position of the @ symbol is returned by the SEARCH function. The characters following the @ must now be extracted. The overall length of the string is then calculated by deducting the characters up to the @ sign. It tells us how many characters make up the domain name on the right.

3. How to extract text from a cell between two instances of a character?

Use the following general formula to obtain a substring between two instances of a certain character:

=MID(cell, SEARCH(“char”, cell)+1, SEARCH (“char”, cell, SEARCH (“char”, cell)+1) – SEARCH (“char”, cell)-1)

The first two justifications for using this MID formula are obvious.

The cell with the original text string is called Text.

Start_num (beginning point): A straightforward SEARCH formula yields the location of the desired character, to which you add 1 because you want to begin extraction with the following character.

The most challenging element is Num_chars (number of characters to extract):

By nesting one Search function inside another, you may first determine the location of the second occurrence of the character.
Then, since you don’t want the delimiter character in the resultant substring, subtract 1 from the result and subtract the location of the first occurrence from the position of the second occurrence.

Use this formula, for instance, to extract text enclosed by two hyphens:

=MID(A2, SEARCH(“-“,A2) + 1, SEARCH(“-“,A2,SEARCH(“-“,A2)+1) – SEARCH(“-“,A2) – 1)

The outcome is seen in the screenshot below:

You can use a more condensed SEARCH SUBSTITUTE combination to retrieve the character’s position if you’re trying to extract content between the second and third or third and fourth instances of the same character.

=FIND(CHAR(1),SUBSTITUTE(cell,character,CHAR(1),Nth occurrence))

In our situation, we might use the following formula to extract a substring between the second and third hyphens:

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

4. How to find substrings in Excel by Formula?

When you simply want to locate cells that include a substring and don’t want to extract it, you use the SEARCH or FIND function as in the aforementioned instances, but you search inside the ISNUMBER function. The Search method returns the position of the first character in the substring if a cell has it, and as long as ISNUMBER receives any integer, it returns TRUE. The search fails if the substring is not discovered, which forces ISNUMBER to return FALSE.

=ISNUMBER(SEARCH(“substring”, cell))

Assume you wish to locate emails that include the substring “gmail” in a list of emails in column A. Use this formula to get it done:

=ISNUMBER(SEARCH(“gmail”, A2))

The outcomes will appear somewhat like this:

Use the following formula inside the IF function to return your own message rather than the logical values TRUE or FALSE:

=IF(ISNUMBER(SEARCH(“gmail”,A2)),”Yes”,”No”)

The formula yields “Yes” if a cell has the substring and “No” otherwise:

The Excel SEARCH function is case-insensitive, as you may recall, so you use it when the character case is irrelevant. Select the case-sensitive FIND function to have your formula recognize uppercase and lowercase characters.

Text to Columns to Extract Substring from Cell in Excel

In Excel, the “Text to Columns” feature can be used to split the contents of one cell into multiple cells based on a specific delimiter or fixed width. This feature is particularly useful for extracting substrings from a cell. Here’s how to use “Text to Columns” to extract a substring from a cell based on a delimiter:

Select the Cell(s): First, select the cell or range of cells that contain the text you want to split.
Go to Data Tab: Click on the ‘Data’ tab on the Ribbon.
Text to Columns: Find the ‘Text to Columns’ button in the ‘Data Tools’ group and click on it. This will open the Text to Columns wizard.
Choose Delimited or Fixed Width:
- Delimited: Select this option if your data is separated by characters such as commas, tabs, spaces, etc. Click ‘Next’ after selecting this option.
- Fixed Width: Select this if you want to split the part of a text based on a specific number of characters. You would then manually set the column breaks in the data preview window.
Set Delimiters: If you chose ‘Delimited’, select the delimiter that your data is separated by. Common delimiters include commas, tabs, semicolons, spaces, or you can specify another character. If there are consecutive delimiters, you can choose to treat them as one. After setting your delimiter, click ‘Next’.
Set Column Data Format: You can assign data types to your new columns, like General, Text, or Date. You can also choose not to import one or more of the split columns.
Finish: Choose the destination for the new split data. By default, it will overwrite the original data, so you may want to set it to a new location. Then, click ‘Finish’.

For example, if you have a cell containing the text “2024-03-13_Report”, and you want Excel to extract the date part, you can use the Text to Columns feature with the underscore (“_”) as a delimiter. The date “2024-03-13” will be separated into a different cell from “Report”.

If you need Excel to extract Excel substring based on fixed width (for example, the first 10 characters of a cell), choose the ‘Fixed Width’ option and set the line in the data preview window after the 10th character.

Remember, “Text to Columns” is a powerful tool, but it changes the original data, so make sure you have a copy of the original data before you begin, in case you need it later.

Application of Extract Substrings from Excel

First Name Extraction:
- Use substring extraction to isolate first names from full names in a dataset.
Date Extraction:
- Extract dates from text strings, allowing you to work with date data independently.
Product Code Isolation:
- Extract product codes or IDs from longer product descriptions or SKU numbers.
Domain Extraction:
- Extract domain names from email addresses or URLs for analysis or categorization.
File Path Separation:
- Split file paths to extract file names or folder names for file management tasks.
Custom Data Parsing:
- Create custom formulas using substring extraction to parse and manipulate specific text data as needed.

Extracting substrings in Excel enhances your ability to work with text data, enabling you to isolate and use specific pieces of information more effectively. Whether it’s for data cleaning, analysis, or custom text manipulation, substring extraction is a versatile tool for various tasks.