How to Use Substrings in Microsoft Excel

In this article, we will delve into the world of substrings and explore various techniques to leverage their potential.

Understanding Substrings

A substring refers to a portion of a text string. It can be a single character, a word, or a group of characters within a larger text. By extracting substrings, you can isolate relevant information from lengthy text strings, making data analysis and organization more efficient.

To extract substrings in Excel, you can utilize various functions such as LEFT, RIGHT, MID, FIND, and LEN. Excel does not have a specific SUBSTRING function, but these functions can be effectively used to achieve similar results.

We will explore several methods:

Let’s look at these methods thoroughly!

Download the Excel Workbook below to follow along and understand How to Use Substrings in Microsoft Excel –

download excel workbookSubstring.xlsx

The LEFT function extracts a specified number of characters from the left side of a text string. To use it, follow these steps:

Select the cell where you want the extracted substring to appear.

Enter the formula =LEFT(A1,3) A1 is the cell reference or the actual text string. In our case, it is January.

3 is the specific number of characters extracted. In our example it is “JAN”. If you put in 4, it would be JANU, etc.

Press Enter to apply the formula.

RIGHT Function

Similar to the LEFT function, the RIGHT function extracts a specified number of characters from the right side of a text string. Here’s how to use it:

Select the cell where you want the extracted substring.

Enter the formula =RIGHT(B1,5)

B1 is the cell reference or the actual text string. In this case, it is February.

5 is the specific number of characters extracted. In our example it is “RUARY”. If you put in 4, it would be UARY, etc.

Press Enter to apply the formula.

MID Function

The MID function allows you to extract a specific number of characters from any position within a text string. Follow these steps:

Select the cell where you want the extracted substring.

Enter the formula =MID(C1,2,3)

C1 is the cell reference or the actual text string. In this case, it is March.

2 in the formula is the starting position of the substring. In the case of March, it would be the second letter “A”.

3 is the number of characters to extract.

Press Enter to apply the formula.

FIND Function

The FIND function helps you locate the position of a specific character or text within a text string. This information is valuable when you need to determine the starting position for extracting a substring. Here’s how to use it:

Select the cell where you want to display the position.

Enter the formula =FIND(“excel”, D1)

“Excel” is the character or text you want to locate.

D1 is the cell reference or the actual text string.

Basically you are asking to find “Excel” in cell D1.

Press Enter to apply the formula. It will show you the position of which the word “Excel” starts.

LEN Function

The LEN function calculates the length of a text string, which is useful when you need to determine the number of characters in a cell. Follow these steps:

Select the cell where you want to display the length.

Enter the formula =LEN(E1)

E1 is the cell reference or the actual text string.

Press Enter to apply the formula. As you can see, the LEN function returns the number of characters in the word “Mississippi”.

There you have it! By using these substring functions in Excel, you can simply extract and manipulate text strings to meet your specific needs. Whether you need to extract a part of a cell, find the position of a character, or calculate the length of a string, these functions provide the needed tools to finish your tasks with ease.