Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Introduction to Alteryx
      • Power BI
        • Power BI – Beyond the Basics
        • Power BI
      • Qlik Sense
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Sign Up
Home > Microsoft Excel > How to Extract an Excel Substring? – 6 Best Methods

How to Extract an Excel Substring? – 6 Best Methods

Note: This guide on how to extract a substring in Excel is suitable for all Excel versions including Office 365.  

In this guide, I am going to show you how to extract any kind of substring in Excel with ease. 

I’ll cover: 

  1. What is a Substring?
  2. Excel Substring Extraction – An Overview. 
  3. How to Extract an Excel Substring of a Fixed-Length?
    • Extract Substring from the Left Side (LEFT)
    • Extract Substring from the Middle (MID)
    • Extract Substring from the End (RIGHT)
  4. How to Extract Excel Substring of a Variable Length?
    • How to Extract Substring Preceding a Delimiter?
    • How to Extract Substring Succeeding a Delimiter?
    • How to Extract Substring Between Two Delimiters?
  5. How to Check for a Substring in Excel?
  6. How to Extract Excel Substring Having a Specific Text or Character?
  7. Extract Excel Substring Using the Text to Columns Feature
  8. Extract Excel Substring Using Flash Fill

Related: 

How To Find Duplicates In Excel? The Best Guide

Excel Goal Seek—the Easiest Guide (3 Examples)

Create A Pivot Table In Excel—the Easiest Guide

What is a Substring?

Have you ever wondered how to extract part of a text string in Excel? 

Before I tell you how to do it, let’s see what exactly a substring is. 

A substring is nothing but a part of any string. This string can be a pure text string or even be an alphanumeric string.

Let’s say, for example, I have a set of alphanumeric product codes in the format “xxxx-yyyy-zzzz”. The leftmost set of characters indicate the product category, the middle set of characters indicate the regional code and the last set of characters indicate the manufacturer’s code. 

Excel Substring
Excel Substring

Here, each set of four characters is a substring of the entire product code. For example, “xxxx” is a substring of “xxxx-yyyy-zzzz”. 

Now, you will come across situations where you need only part of a code or text. For example, in my case, I need to find the region codes of products from the product codes. To do this, first I need to extract the region substring from the product code. 

Excel Substring Extraction – An Overview.

The most efficient way to extract an Excel substring is to use the Text functions, as there are no dedicated Excel substring functions. 

I’ll cover all possible scenarios of substring extractions and explain each one of them with examples. 

How to Extract an Excel Substring of a Fixed-Length?

Excel has three text functions for extracting text of a certain length from the left, middle and right of a string. They are: 

  1. LEFT 
  2. MID
  3. RIGHT

Extract Substring from the Left Side (LEFT)

To extract a substring of a certain length from the left side of a text, use the LEFT function. 

Here in this example, I enter =LEFT(A2,4) to extract the first four characters of the product code from its left-hand side. 

Extarct Excel Substring from the Left Side using the LEFT function
Extract Excel Substring from the Left Side using the LEFT function

Extract Substring from the Middle (MID)

To extract a substring of a certain length from anywhere in the middle of a string, use the MID function. 

Here in this example, I enter =MID(A2,6,4) to extract four characters starting from the 6th character of the product code. 

Extract Excel Substring from the Middle using the MID function
Extract Excel Substring from the Middle using the MID function

Extract Substring from the End (RIGHT)

To extract a substring of a certain length from the end of a string, use the RIGHT function. 

For example, I enter =RIGHT(A2,4) to extract the last four characters of the product code. 

Extract Excel Substring from the right end using the RIGHT function
Extract Excel Substring from the right end using the RIGHT function

Also Read: 

How To Use Excel Countifs: The Best Guide

Excel Conditional Formatting -the Best Guide (Bonus Video)

The Best Excel Project Management Template In 2021

How to Extract Excel Substring of a Variable Length?

In some cases, things may not be as simple as this. The substring may not have a fixed length.

We cannot use LEFT, RIGHT and MID functions directly in such cases, as these functions need a fixed substring length. 

But, we can use a combination of these along with SEARCH or FIND functions, to extract the substring. Also, it helps a lot if the original text string has some kind of delimiter in it which separates the substrings. For example, a space or a hyphen will do the trick. 

Please note that SEARCH is not case-sensitive whereas FIND is case-sensitive. 

How to Extract Substring Preceding a Delimiter?

To extract a substring that precedes a delimiter, use the formula 

= LEFT(cell_reference, SEARCH(“Delimiter”, cell_reference)-1) inside an adjacent cell and drag it to the entire range of cells. 

In this example, use =LEFT(A2, SEARCH("-",A2)-1) in cell B2 and drag it to the entire data range.
In this example, use =LEFT(A2, SEARCH(“-“,A2)-1) in cell B2 and drag it to the entire data range.

Here, the “Delimiter” can be any character in the string you choose and “cell_reference” is the cell reference of the original text string. 

In this formula, the SEARCH function searches for the delimiter and returns its position in the string. Then the LEFT function extracts the substring up to the delimiter’s position. 

Note: Here, the SEARCH function returns the position of the first instance of the delimiter. 

How to Extract Substring Succeeding a Delimiter?

To extract a substring that succeeds a delimiter, use the formula 

RIGHT(cell_reference,LEN(cell_reference)-SEARCH(“Delimiter”, cell_reference)) inside an adjacent cell and drag it to the entire range of cells. 

In this example, use =RIGHT(A2,LEN(A2)-SEARCH("-",A2)) in cell B2 and drag it to the entire data range.
In this example, use =RIGHT(A2,LEN(A2)-SEARCH(“-“,A2)) in cell B2 and drag it to the entire data range.

In this formula, the SEARCH function searches for the delimiter and returns its position in the string. Then, the RIGHT and LEN functions extract the substring after the delimiter’s position. 

Note: Here, the SEARCH function returns the position of the first instance of the delimiter. If you have more than one delimiter and want to extract the substring after the last delimiter, using the text to columns feature is an easier option. 

How to Extract Text Between Two Delimiters?

The easiest way to extract a substring between two delimiters is to use the text to column feature in Excel, especially if you have multiple delimiters. 

But, if you still want to use a formula to do this, use this: 

=MID(cell_reference, SEARCH(“Delimiter”, cell_reference)+1, SEARCH (“Delimiter”, cell_reference, SEARCH (“Delimiter”, cell_reference)+1) – SEARCH (“Delimiter”, cell_reference)-1)

In this example, use =MID(A2, SEARCH(“-“,A2) + 1, SEARCH(“-“,A2,SEARCH(“-“,A2)+1) – SEARCH(“-“,A2) – 1) in cell B2 and drag it to the entire data range.
=MID(A2, SEARCH("-",A2) + 1, SEARCH("-",A2,SEARCH("-",A2)+1) - SEARCH("-",A2) - 1) 

Here, the MID function has three arguments: cell_reference, starting position and string length. 

In our modified MID formula, in the first two arguments, we give the cell reference and the starting position of the substring after the delimiter. 

For the last argument, we use a nested SEARCH function to find the position of the last delimiter and subtract the position of the first delimiter from it. This yields the length of the substring between the two delimiters. 

There is an alternative formula to do this, especially if you have multiple instances of the same delimiter in the string. 

Use this formula: 

=MID(cell_reference,FIND(CHAR(1),SUBSTITUTE(cell_reference,”Delimiter”,CHAR(1),N))+1,FIND(CHAR(1),SUBSTITUTE(cell_reference,”Delimiter”,CHAR(1),N+1)) – FIND(CHAR(1),SUBSTITUTE(cell_reference,”Delimiter”,CHAR(1),N))-1)

In this example, to extract Excel substring between the 2nd and 3rd Delimiter, use the modified form of the formula mentioned above.
In this example, to extract Excel substring between the 2nd and 3rd Delimiter, use the modified form of the formula mentioned above.

Where N and N+1 are the nth and n+1th number of occurrences of the delimiter in the original string, between which lies the substring of interest. 

For example, if you want to extract the substring between the 3rd & 4th occurrences of a delimiter, replace N and N+1 with 3 and 4 respectively. 

How to Check for a Substring in Excel?

In certain scenarios, you may need to only check whether a particular substring is present in the text or not. Do this easily with an ISNUMBER and SEARCH combo. Replace SEARCH with FIND if you want case sensitive results. 

Type in the formula =ISNUMBER(SEARCH(“substring”, cell_reference)) in an adjacent column cell and drag it to the entire data range. 

Excel Substring checker
Excel Substring check using ISNUMBER and SEARCH

Here, the SEARCH function looks for the substring in the text and returns a numerical value denoting the position of the substring only if it is present. It returns an error if the substring is not present in the text. So, the ISNUMBER function returns TRUE or FALSE results based on the SEARCH function’s inputs. 

How to Extract Excel Substring Having a Specific Text or Character?

In situations where you are looking to extract a substring which contains a specific text or character, type this formula in the adjacent column:

=TRIM(MID(SUBSTITUE(cell_reference,” ”,REPT(“ “,100)),MAX(1,FIND(“Character”, SUBSTITUE(cell_reference,” ”,REPT(“ “,100)))-50),100)) 

Use the appropriate modified form of the formula and drag it to the entire data range. Excel Substring
Use the appropriate modified form of the formula and drag it to the entire data range.
=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),MAX(1,FIND(".", SUBSTITUTE(A2," ",REPT(" ",100)))-50),100))

Here, “Character” is the text or character of interest you are looking for. The numbers 100 AND 50 are arbitrary. Use any large number you want, as long as you replace “50” with half of the number of your choice. 

Let me break down this formula into three simple steps. 

Step 1: SUBSTITUTE and REPT combo replace each space with 100s of spaces. 

Step 2: MID and FIND combo looks for the specific and character you are looking for and

extracts the substring which contains it. The MAX function makes sure that the MID

 function doesn’t start in a negative position for cases where the character is present in 

the first word of the text. 

Step 3: The TRIM function removes the extra spaces in the extracted substring. 

Note: This formula works only when you have a space as the delimiter in the text string. If you have any other delimiter, break it into two steps. First, use the same formula as above up to the MID function. Then, replace TRIM with another SUBSTITUTE and choose “” as the replacement. 

Extract Excel Substring Using the Text to Columns Feature

This is another easy way to extract a substring in Excel. Just select the relevant data range and click on the Text to Columns button under the Data tab. Select the relevant delimiter and click OK in the Text to Columns wizard.

Extract Excel Substring using text to columns
Locate and Click on the Text to Columns button under the Data Tab

Click on the Delimited option in the Wizard menu and Click Next
Click on the Delimited option in the Wizard menu and Click Next

Choose your Delimiter
Choose your Delimiter

Select your Destination
Select your Destination

Substrings are separated successfully
Substrings are separated successfully

There are two major disadvantages to this method. It extracts all substrings present in the string and it is not dynamic. 

Extract Excel Substring Using Flash Fill

This is arguably the easiest method to extract a substring in Excel. Just manually type in the substring for the first few instances and drag it to the rest of the data range. Let Excel Flash Fill show its magic and fill all the substrings for you. 

Extract Substring using Flash Fill
Extract Substring using Flash Fill

Note: This method is not dynamic and may not work in some cases. Also, Flash Fill is available only in Excel versions 2013 and later. 

Suggested Reads: 

Create An Excel Dashboard In 5 Minutes – The Best Guide

Dynamic Dropdown Lists In Excel – Top Data Validation Guide

Predict Future Values Using Excel Forecast Sheet – The Best Guide

Let’s Wrap Up

These are all the different Excel substring methods. If you have any questions about this or any other Excel feature, let us know in the comments. 

If you need more high-quality Excel guides, please check out our free Excel resources centre.

Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 100+ IT training courses. Click here for advanced Excel courses with in-depth training modules.

Adam Lacey

Adam Lacey is an Excel enthusiast and online learning expert. He combines these two passions at Simon Sez IT where he wears a number of different hats. When Adam isn't fretting about site traffic or Pivot Tables, you'll find him on the tennis court or in the kitchen cooking up a storm.

30 day vertical banner

Most Popular Posts

  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15
  • How to Create Charts and Graphs in Microsoft Excel 2016

Similar Posts

The Best Excel Project Management Template in 2021

How to Insert Hyperlinks in Visio 2010

How to Recover a Word Document ? 4 Proven Ways

Customizing Tables in Microsoft Project 2013

Using the LET Function in Excel

Using the Tell Me Feature in Microsoft Excel 2016

Course Categories

  • Web Development
  • QuickBooks
  • Microsoft
  • Adobe
  • Data Analysis

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebook
© 2022 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)