How to Extract Substrings and Text in Excel

When working with text strings in Excel, you’ll often need to extract and manipulate substrings – whether it’s cleaning up data, parsing out parts of text, or performing text analysis. Luckily, Excel provides several straightforward ways to extract substrings and characters from strings.

This guide will walk through the key methods to extract substrings and text using Excel functions and features like LEFT, RIGHT, MID, FIND, SEARCH, and Flash Fill. Follow along to learn how to master substring extraction in Excel.

Why Extract Substrings in Excel?

Here are some common use cases for extracting substrings in Excel:

• Data cleaning – Extracting consistent substrings from long strings can help clean up and standardize messy data For example, parsing out ZIP codes or ID numbers

• Text parsing – Breaking down strings into substrings enables analyzing the component parts. Like parsing names into first/last or extracting domains from URLs.

• Text manipulation – Grabbing substrings allows you to reorder, replace or modify components of larger strings

• Key field extraction – Identifying and extracting substrings from critical fields like product codes helps with categorization.

• String aggregation – Combining substrings together can construct new concatenated strings for reporting.

Whether you need to clean up data, dissect text, or extract key parts of strings, Excel provides easy ways to get the substrings you need.

Excel Substring Functions

Excel includes several functions that can extract substrings by specifying a starting position and number of characters to return.

LEFT, RIGHT and MID

The LEFT, RIGHT and MID functions are the key substring extraction functions.

• LEFT – Returns characters from the start of a string
• RIGHT – Returns characters from the end of a string
• MID – Returns characters from the middle of a string

The basic syntax is:

=LEFT(text, num_chars)

=RIGHT(text, num_chars)

=MID(text, start_num, num_chars)

For example, =LEFT(A2, 5) would return the first 5 characters in cell A2.

You can nest these together like =LEFT(RIGHT(A2, 10), 3) to extract substrings from substrings.

FIND and SEARCH

The FIND and SEARCH functions locate one string within another string and return its starting position. This enables extracting substrings based on delimiters.

For example, if cell A2 contained “Excel_Tips.docx”, then:

=LEFT(A2,FIND("_",A2)-1) would extract “Excel”

=RIGHT(A2,FIND(".docx",A2)-1) would extract “Tips”

This approach is powerful for extracting components of strings with consistent delimiters like underscores, periods, commas, etc.

Using MID and FIND

A common use pattern is combining MID and FIND to extract a substring based on delimiters:

=MID(text, FIND(start_delimiter, text) + length(start_delimiter), FIND(end_delimiter, text) - FIND(start_delimiter, text) - length(start_delimiter))

It first uses FIND to locate the starting delimiter, then captures everything between the start and end delimiter based on their positions.

For example, to extract the domain from email addresses:

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

This formula extracts the text between the @ and . in the email string.

Additional Substring Functions

Here are a few more Excel functions that can help extract characters or substrings:

• TRIM – Remove leading, trailing and excess spaces from text
• CLEAN – Remove non-printable characters like tabs and page breaks
• SUBSTITUTE – Replace or remove substrings
• REPLACE – Replace characters within a substring
• REPT – Repeat strings to a specified length

Using Flash Fill

Excel’s Flash Fill feature can automatically extract substrings and parse text based on examples, saving you from writing formulas.

To use Flash Fill:

1. Enter examples of the extracted text into adjacent columns

2. Select the columns

3. Go to Data > Flash Fill

4. Excel will automatically fill down the extracted text

Flash Fill uses pattern recognition to parse and extract substrings intelligently. It’s a quick way to split text without formulas.

Text to Columns

Another simple option: use the Text to Columns wizard (Data tab) to split text into multiple columns by a delimiter like a comma or space.

This can instantly separate string components into discrete columns. Useful for parsing structured text like CSV data.

VBA Substrings

You can also leverage VBA functions to extract substrings in Excel macros:

• Left – Same as LEFT worksheet function
• Right – Same as RIGHT worksheet function
• Mid – Same as MID worksheet function
• InStr – Find position of substring
• Split – Split string into array by delimiter

VBA provides added flexibility for substring manipulation.

Extracting Substrings with Power Query

In Power Query, you can extract substrings from text columns using:

• Text.Start – Extract start of string
• Text.End – Extract end of string
• Text.Middle – Extract middle substring
• Text.BeforeDelimiter – Extract before delimiter
• Text.AfterDelimiter – Extract after delimiter
• Text.BetweenDelimiters – Extract between two delimiters

Power Query offers robust text parsing capabilities to integrate, cleanse, and transform data from multiple sources.

Summary

Excel has all the capabilities needed for flexible substring extraction. The key takeaways are:

• LEFT, RIGHT and MID extract a specified number of characters from the start, end or middle of text.

• FIND and SEARCH identify delimiter positions required by functions like MID.

• Flash Fill can automatically parse and extract substrings with pattern recognition.

• Text to Columns separates text into columns by delimiters.

• Power Query provides advanced text parsing transformations.

With these tools, you can cleanly extract the substrings, characters and text components needed for your raw data cleansing, text manipulation and analytics needs.

So next time you need to slice and dice text in Excel, grab just the substring you want!