Need to extract specific parts of text from an Excel cell? This guide and practice spreadsheet will show you how, whether it’s an address, phone number, or part of a product description. We’ll explore straightforward techniques, including Excel formulas, to isolate your text and make your Excel data easier to manage.
What’s a Substring in Excel?
Let’s clarify some items before we have Excel extract text from string. First, many programming languages have dedicated substring functions. Microsoft Excel does not, although some developers have created 3rd party add-ons. So instead, we’ll rely on Text functions.
When we speak of a substring, we mean a part or subset of the Excel cell’s content. For example, if our cell contains the original string 1001 Drake Ave., any of these items could be a substring:
- 1001
- Drake Ave.
- 100
- rake
Typical Text String Mess
For this guide, I’ll use a spreadsheet with street names and numbers in the same cell. This format works fine if you’re creating a mailing label, as the post office relies on zip code sorting. But what if you need to do door-to-door canvassing? Maybe you have a group of volunteers and assign each one a neighborhood represented on a printed sheet.
If you open this list in Excel and sort on the Street column, you get a numerically sorted list. As you can see in the example below, the Drake Ave records are not together. Our volunteers would go all over town based on this sort.
Ideally, you want to sort the list so the Drake Ave. entries are together. There are several ways to do this in Excel, but one way is to create two columns from the original alphanumeric string. You can’t easily extract text using Excel Text to Columns wizard since we have repeating delimiters.
The first column reflects the street number substring, and the second is the street name substring. You can then sort the list based on the street name and number.
Microsoft 365 Extract Text Functions
One benefit to using Microsoft 365, is it has additional functionality. If you have this subscription, then I would start with TEXTBEFORE and TEXTAFTER. Both these functions make it easy to extract text from a cell. They are the easiest and don’t suffer from some flash fill quirkiness.
Use TEXTBEFORE to Grab Street Number
The TEXTBEFORE function is true to its name. It grabs the contents of a cell before a specific delimiter. In our case, we’ll be using the space between the street number and the street name.
- Open your spreadsheet or use the sample spreadsheet in the Resource section.
- In cell D1, type NBR.
- In cell E1, type STREET NAME
- In cell D2, type the following formula:
=TEXTBEFORE(C2, " ",1,1)
With this formula, I’m asking Excel to extract the text before the space in cell C2. The first 1 represents the “instance number” of the delimiter. In my scenario, I want the first space. However, if I change the value to 2, the function would extract 1001 Drake because it would use the space between Drake and Ave. The second 1 in the formula indicates the match mode. The 1 is for case insensitive.
TEXTAFTER to Grab Remainder
You can think of TEXTAFTER as the compliment to TEXTBEFORE. The function works similarly except it extracts part of the text after your delimiter. In our scenario, we want to extract everything past the first space between the number and street name.
=TEXTAFTER(C2," ",1,1)
If you get confused with any of the parameters, you can always use the option of adding parameters using the Function Arguments dialog box. The dialog is nice since it provides context and highlights the result.
Building a Nested Extract Formula
For those that don’t have Microsoft 365,, we’ll nest some Excel functions such as LEFT and FIND. As we progress, we’ll add several sets of parentheses. By nested, I mean we’ll use one function (FIND) as an argument for another function, such as LEFT or RIGHT.
Using the FIND Function
Let’s start with =FIND(" ",C2). In plain text, our function syntax asks Excel to look in cell reference C2 for a single space represented by the “. ” I added a starting position of “1” in the picture below, but this is an optional parameter, and Excel starts at 1 by default. Excel found the blank space in position 5, which is shown in cell D2.
I’ll remove the optional starting parameter of 1 to simplify the formula since Excel starts there anyway.
In this scenario, I used FIND, which is case-sensitive. Microsoft has a SEARCH function, which is case-insensitive.
Using the LEFT Function
Now, let’s add the LEFT function so that our formula reads =LEFT(C2,(FIND(" ",C2))). In this instance, we’re again using cell C2, but the LEFT function will grab the cell contents in C2 from the leftmost character in position 1 to where the Excel FIND function found the blank space.
However, there is a minor issue. While you can’t see it, there is a trailing space in D2. Using the LEN function, we can see cell D2 has 5 characters. You may remember this handy function from our tutorial on how to check the character count in Excel.
The solution is to subtract 1 or use the TRIM function I referenced in how to separate names in Excel. For simplicity, I’ll use the -1. While the visual results are the same, the character count dropped by 1.
How to Extract Street Numbers Using LEFT Function
- Import your data into Microsoft Excel or use the sample spreadsheet in the Resource section.
- In cell D1, type Nbr.
- In cell E1, type Street Name
- In cell D2, type the following formula:
=LEFT(C2,(FIND(" ",C2)-1))
- Press Enter. The value 1001 should show in D2.
The next part involves copying this formula to the rest of the entries. First, we must reference the correct street cell and not use C2 for the remaining rows.
- Click cell D2 to select the beginning of our range.
- Move your mouse to the lower right corner.
- Double-click the + cursor in the lower right. This will copy your formula down the column.
In column D, you should see the extracted street numbers.
How to Extract Street Names Using the RIGHT Function
Using the RIGHT function, we’ll create a similar nested formula to capture the street address. This time, we will grab the contents from the Street column to the right of the first space.
- In cell E2, type the following formula
=RIGHT(C2,LEN(C2)-FIND(" ",C2))
- Press Enter. E2 should show as Drake Ave.
- Click cell E2 to select the beginning of our range.
- Move your mouse to the lower right corner.
- Double-click the + cursor in the lower right. This will copy your formula down the column.
Columns D and E should contain the parsed contents from your original street address.
Your spreadsheet should look similar to the one below.
The spreadsheet now has your split fields, but you may want clean up the formulas. For example you might want copy formula values in Excel to values.
These Excel formulas to extract text from cell are key skills for efficient data management. They can simplify the process of sorting and analyzing spreadsheet data. Utilizing these functions effectively can lead to more productive and organized data handling. While we focused on using a space as our delimiter, they can be modified to handle your scenarios. Now, it’s your turn to practice.