Pinterest Pixel

How to Use Substrings in Microsoft Excel

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

Download Excel Workbook
John Michaloudis
Posted on

Overview

How to Use Substrings in Microsoft ExcelIn 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

LEFT Function

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.

See also  ADDRESS Formula in Excel

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.
How to Use Substrings in Microsoft Excel

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.

How to Use Substrings in Microsoft Excel

MID Function

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

See also  How to Move Columns in Excel - 3 Easy Ways!

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.

How to Use Substrings in Microsoft Excel

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.
How to Use Substrings in Microsoft Excel

See also  Quick Language Change in PowerPoint: Spell Check Guide

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”.

How to Use Substrings in Microsoft Excel

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.

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

See also  Excel Extract First Name From Full Name
Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!