In Excel, a substring is a specific part of a text which is contained within a cell. Once you’ve input data into an excel spreadsheet, each cell contains explicitly a specific part of the data. The whole data contained within the cell is known as a string and with the help of substrings, you can target specific sections of data from the whole string.

For instance, if you have written up data such as email addresses in an excel spreadsheet, you can extract only the username section of the emails, i.e. everything before @ symbol. This skill comes in handy at times when you want to find and use specific information from cells present in the spreadsheet.

What are the Excel TEXT functions?

Although there are not any specific functions in Excel for extracting substrings, you can perform the operation using a number of TEXT functions. You can extract the text using a specific syntax or pattern for each function.

There are three primary functions that are used to extract a substring of text from a cell.

• LEFT Function: It helps in extracting a substring from the left of the text string.
• RIGHT Function: It helps in extracting a substring from the right of the text string.
• MID Function: It helps in extracting a substring from the middle of the text string, which begins at a specified position in the text.

There are many other functions like FIND, LEN, and TRIM which are helpful to find the position or length of a specific text in a cell.

LEFT Function in Excel

The LEFT function in Excell allows you to separate and extract a substring, starting with the leftmost character and ending after several characters that you specify.

The syntax for the LEFT function:

=LEFT (text,[num_chars])

• text: References a cell that contains the characters you wish to extract or specifies text string.
• num_chars: Indicates the number of characters you want to extract (also includes the space between the characters).

Example of Left Functions:

To understand in a better way, let us look at the following example.

• In the cell A1 of the Excell spreadsheet, we have an address, let’s assume that we only want the door number and street name from the address, for this we would require the Left function.

=LEFT (A1,14)

• Once you have put in the Left Function and the number of characters you wish to have in the cell ( including the space), you will then get the desired output.
• In this case, we wish to have only the door number and street name so after indicating the cell i.e A1 we then put the 14 value as in total there are 14 characters starting from left (which includes space).

How to Extract Substring before specific Character(s)

If you have data in an excel spreadsheet that has a specific pattern in it, the FIND, SEARCH and LEN functions would come in handy to separate the text from one specific character and there would not be any need of counting the number of characters.

FIND function.

=LEFT(text,FIND(“char”,text)-1)

• text: References a cell that contains the characters you wish to extract or specifies text string.
• char: Determines a specific character that specifies the position.

With the help of the FIND function, you can find the position of a specific character and subtract 1 character ( the character that you input itself) and you will get the result as all the characters present on the left-hand side of that character.

For instance, if you have data on email addresses and you only want the username from the email address, which means everything that comes before the sign @, you can do it easily with the formula.

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

• With the help of the FIND function formula, we easily got the result as the characters that are present right before the @ sign which is 9 characters but 1 character is subtracted i.e @, which means 8 characters are been subtracted from the text string.

SEARCH, LEN function.

We can also use the SEARCH, LEN function to get the same result, you can simply subtract the portion from the text string after you have specified the character till where you have to subtract. The total number of characters you will get into the result will then depend upon that.

=LEFT(text,LEN(text)-SEARCH(“char”,text))

Let us use the SEARCH function in the case where we have to extract the substring before ‘The’ comes in the text string. The syntax for the same would be.

=LEFT(A2,LEN(A2)-SEARCH(” The”,A2))

• After using the SEARCH function, 19 characters were subtracted from the text string which was present before ‘The’, with the help of the LEN function we found there are 37 characters and out of which 18 characters are extracted from the left side the text string with the help of LEFT function.

Take into consideration that along with ‘ The ‘ word we also tool one space which is considered to be 1 character two times as the argument for the SEARCH function.

Note – The position of the first character will be returned if we use multiple characters in the first argument of the SEARCH or FIND function.

Right Function in Excel

The RIGHT function in Excell allows you to separate and extract a substring, starting with the rightmost character and ending after several characters that you specify.

The syntax for the RIGHT function :

=RIGHT(text,[num_chars])

Example of Right Functions:

To understand in a better way, let us look at the following example.

• In cell A2 of the Excell spreadsheet, we have data of the full names of people, let’s assume that we only want the Surname from the full name, for this, we would require the RIGHT function.

=RIGHT(A2,7)

• Once you have put in the RIGHT Function you will then get the desired output which is only the Last Name (Surname).
• In this case, we wish to have only the Last Name so after indicating the cell i.e A2 we then put the 7 value as in total there are 15 characters starting from Right(which includes space).

How to Extract Substring before specific Character(s)

If you have data in an excel spreadsheet that has a specific pattern in it, the FIND, SEARCH/LEN functions would come in handy to separate the text from one specific character and there would not be any need of counting the number of characters.

SEARCH/LEN function:

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

FIND/LEN function:

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

The FIND function returns the position of the character “@,” which is 9; the LEN function determines the length of the entire string; and the RIGHT function extracts the resulting number of characters from the string’s right side.

MID Function

The MID function in Excell allows you to separate and extract a substring, from, the middle of the text string starting at the characters that you specify.

The syntax for the LEFT function :

=MID(text,start_num,num_chars)

The MID function has a somewhat different set of arguments than the other two Text functions. You must additionally include the starting position (start num) in the original text string in addition to the original text string (text) and the number of characters to extract (num chars).

Example of MID Function:

To understand in a better way, let us look at the following example.

• Let’s extract middle names from the Excell spreadsheet of the list of full names.

=MID(A2,9,4)

• With the help of the MID function, you will get the output from the 9th character and would consider having 4 characters in it starting from the 9th character.

How to Extract Substring Between Two Character(s)

1. If you want to extract a substring between two characters use the syntax mentioned below.

=MID(text,FIND(“char”,text)+1,FIND(“char”,text)-FIND(“char”,text)-1)

• text: References a cell that contains the characters you wish to extract or specifies text string.
• num_chars: Indicates the number of characters you want to extract (also includes the space between the characters).

For instance, if you havce an email adress and want to have domain name without the .com part you can use the syntax given below.

=MID(A2,FIND(“@”,A2)+1,FIND(“.”,A2)-FIND(“@”,A2)-1)

• Let’s break down the whole formula located in the above example.
• A2 Indicates the cell which contains the text string from which we wish to extract the substring.
• FIND(“@”,A2)+1 helps in finding out the position of the substring i.e 9, located just right after the ‘@‘ sign.
• FIND(“.”,A2)-FIND(“@”,A2)-1 specifies the length of substring between the ‘@’ sing and the ‘.’ by subtracting the position of ‘.’ i.e 21 from the position of the ‘@’ i.e 8 and subtracting 1 from the whole output.

Let’s take a look at another example using the formula:

=MID(A2,FIND(” “,A2)+1,FIND(” The”,A2)-FIND(” “,A2)-1)

• FIND(” “,A2)+1 – determines a space character in the first part of the argument.
• FIND(” The”,A2)-FIND(” “,A2)-1  specifies a space character, and the word ‘The’ are used together as the first argument in the first FIND function, and space is used as the first argument in the second FIND function.