How to Use the Substring Formula in Excel: A Complete Guide

Sharing is caring!

Are you looking to extract a specific portion of text from a cell in Microsoft Excel? The substring formula allows you to pull a certain number of characters from a text string, starting at the position you specify. This can be extremely useful for extracting key pieces of information from longer strings of text. In this article, we’ll cover everything you need to know about using the substring formula in Excel.

What is the Substring Formula in Excel?

The substring formula, also known as the MID function, allows you to extract a specific number of characters from a text string in an Excel cell, beginning at the starting position you indicate.

The syntax for the substring/MID formula is:

=MID(text, start_num, num_chars)

Where:

• text is the cell containing the full text string you want to extract a substring from
• start_num is the position of the first character you want to extract in the text string
• num_chars is the total number of characters you want to extract

For example, if cell A1 contains the text string “Excel Formula”, to extract the word “Formula” you would use:

=MID(A1,7,7)

This starts at the 7th character in A1 and extracts the next 7 characters, giving you “Formula”.

When to Use the Substring Function

There are many scenarios where you may need to pull out a portion of text from a longer string in Excel:

• Extracting a person’s first or last name from their full name
• Pulling a specific part number or ID from a product SKU or serial number
• Getting an area code from a full phone number
• Isolating a particular piece of data from a lengthy report or data export

Any time you have text data where the piece you need is buried inside a longer string, the substring function can help you extract it into its own cell. This allows you to easily use that specific data point elsewhere in your spreadsheet.

How to Use Substring in Excel

Using the substring formula is fairly straightforward once you understand the syntax. Let’s walk through a more detailed example.

Imagine you have a spreadsheet containing full names, and you want to split out the first and last names into separate columns.

To extract the first names, you would use:

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

Here’s what this does:

1. Looks at cell A2 which contains “John Smith”
2. Starts at the 1st character
3. Uses FIND(” “,A2) to locate the position of the first space ” ” in the string. This will be where the first name ends.
4. Subtracts 1 from the space position to get the number of characters to extract – everything before the space.

So this will pull “John” from “John Smith”. You can copy this formula down to extract the first name for each person.

To get the last names, you would use:

=MID(A2,FIND(” “,A2)+1,LEN(A2))

This is similar, but it:

1. Starts at the character after the space, using FIND(” “,A2)+1
2. Extracts the total number of characters from that point to the end, using LEN(A2) to get the total length

So using the substring/MID function and a little creative finding of the starting position and number of characters, you can parse out different sections of a text string.

Substring vs LEFT, RIGHT Functions

In addition to MID, Excel has LEFT and RIGHT functions for extracting characters from the start or end of a string.

• LEFT(text, num_chars) pulls a set number of characters from the beginning of a string
• RIGHT(text, num_chars) pulls from the end

In our names example, if all the first names were the same length, we could have used LEFT to get them:

=LEFT(A2,4)

Would pull the leftmost 4 characters, extracting “John” from “John Smith”.

And if all the last names were the same length, we could use RIGHT:

=RIGHT(A2,5)

Would take the rightmost 5 characters, giving us “Smith”.

So when you have a consistent number of characters to pull from the start or end, LEFT and RIGHT can be easier than MID. But when you need something from the middle of a string or a varying number of characters, MID is essential.

Combining Substring with Other Functions

To really leverage the power of substring, you’ll often combine it with other Excel functions like FIND, SEARCH, LEN, etc. These allow you to dynamically locate the starting position or determine the number of characters to extract.

Some common combinations:

Finding substrings:

• FIND(substring, text) gives you the position of substring within text
• SEARCH(substring, text) is similar but case-insensitive

So in our example, FIND(” “,A2) found the first space in the full name to determine the end of the first name.

Getting string lengths:

• LEN(text) returns the total number of characters in text

We used this to extract from the space to the very end of the full name string for the last name.

Nesting functions:
You can nest your substring formula inside other functions too. For example:

=PROPER(MID(A2,1,FIND(” “,A2)-1))

Would extract the first name and then apply the PROPER function to capitalize the first letter, giving you “John” instead of “john”.

The possibilities are endless when you start combining substring with other functions to parse and manipulate text in Excel. It’s an extremely powerful tool in your spreadsheet toolkit.

Substring Formula Not Working?

If your substring formula isn’t working, check a few key things:

1. Make sure the syntax is correct: =MID(text, start_num, num_chars)
2. Check that start_num isn’t larger than the total length of the string
3. Ensure num_chars doesn’t go beyond the end of the string
4. Watch out for extra spaces before/after your search string if using FIND
5. Use absolute cell references (like $A$2) if copying formulas to avoid the cell changing

Paying close attention to the syntax and remembering to lock your cell references if copying the formula are the most common fixes when substring isn’t behaving as expected.

Conclusion: Master the Excel Substring Formula

The substring or MID function in Excel is a powerful way to extract specific portions of text from longer strings. By identifying the starting position and number of characters to pull, you can parse out names, IDs, codes, and more. Substring pairs perfectly with functions like FIND, LEN, LEFT and RIGHT for even more text manipulation power.

Mastering substring will allow you to clean up and separate your raw data in all kinds of flexible ways. Any time you need to consistently extract a particular portion of a text string in Excel, substring should be your go-to tool. With a little practice, you’ll be slicing and dicing your text data with ease.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.