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(
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)
Hit Enter, and you’ll see Excel pull the first two numbers from the value in cell A2, and place them in cell B2.
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)
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.
Multiply in Excel
»