• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

Excelypedia

The Excel Encyclopedia of The Internet

  • Tutorials
  • Functions
  • Formulas
  • Templates

Substring in Excel

April 6, 2021 by Andrew Childress
Substrings Excel

Sometimes, it's helpful to extract part of a string of characters into a separate cell. Let's learn how to find a substring in Excel.

Sometimes, it’s helpful to extract part of a string of characters into a separate cell. This is easy to do. Let’s learn how to find a substring in Microsoft Excel.

How to Find Substrings in Excel

Imagine you have a column of seven-digit numbers. These are order numbers, and different parts of the string represent different values. The first two digits are country codes. The middle three are customer codes. The last two are product ID numbers. You want to break out each of these into its own column.

For country codes, click into cell B2. Type =, which tells Excel that you’re beginning a formula. Then, type LEFT. This tells Excel that you want to pull from the left side of the string. Then, type (. So far, your formula is:

=LEFT(

Substring Excel

Next, click into cell A2. This is the cell reference. Now Excel knows to look at the left side of the value in cell A2. Type a comma. Then, you need to tell Excel how many characters to pull from the referenced value. Here, we want the first two characters, so type 2, then close with ). Your formula is:

=LEFT(A2,2)

Substrings Excel

Hit Enter, and you’ll see Excel pull the first two numbers from the value in cell A2, and place them in cell B2.

Substring in Excel

To find the customer codes, in cell C2, start by typing:

=MID(A2,

Here, we want the middle three digits. These start on the third number in the sequence, so type 3,. Finally, Excel asks for how many values to return. Here, we want 3, so type 3), completing your formula as:

=MID(A2,3,3)

Hit Enter again, and you’ll see the middle three values in cell C2.

Finally, in cell D2, you’ll want to find the two rightmost values. The formula here is:

=RIGHT(A2,2)

How to use substring in Excel

Once again, pressing Enter returns the two values on the right side.

Click and drag the lower right corner of each cell containing a formula to fill in the rest of your table. This shows how using substring formulas saves you time and work when you’re analyzing data in Excel.

Category: Excel FormulasTag: Excel, How To, Instructions, Tutorial

About Andrew Childress

Previous Post: « Multiply Excel Multiply in Excel
Next Post: Subtract in Excel Sorted subtraction Excel »

Sidebar

Recent Posts

  • Format Cell Alignment in Excel
  • Default Width in Excel
  • Translate in Excel
  • Switch Windows in Excel
  • EXACT in Excel

Categories

  • Basics
  • Examples
  • Excel Formulas
  • Excel Functions
  • Excel Shortcuts
  • Excel Templates
  • Excel Tutorials
  • Uncategorized

Excelypedia

Master Excel to add a highly valuable asset to your employability portfolio. Learn how to analyze data in Excel.

  • Facebook
  • Twitter
  • Instagram
  • LinkedIn
  • YouTube

Learn

  • Functions
  • Formulas
  • Shortcuts

Legal

  • Privacy Policy
  • Cookie Policy
  • Terms & Conditions

Help

  • FAQ
  • Contact Us

Copyright © 2022 · Excelypedia · All Rights Reserved

Back to top