How to Extract a Number or Text from Excel with this Function

Excel is great at working with both numbers and text—but if you’re using both in the same cell, you might run into some difficulty. Fortunately, you can extract numbers or text from cells to work with your data more efficiently. We demonstrate several options, depending on the format that your data is currently in.

Numbers Stored as Text

This is a common situation, and—fortunately—very easy to deal with. Sometimes cells that contain only numbers are incorrectly labeled as text, preventing Excel from using them in operations. You can see in the image below that the cells in column A are stored as text, as indicated by the green flag on each cell as well as the format box, both of which are highlighted below.

Convert to Number

To solve the problem, just click into a cell, click the warning box, and select “Convert to number.” That’s all there is to it! This can be done on multiple cells by selecting them all and clicking on the warning box, but this likely isn’t as efficient as the following method.

Text to Columns

If you have a lot of cells that need to be fixed, using this method might take a monumental amount of time. To get through the process faster, you can use Excel’s Text to Columns function. Select the numbers that you want to convert, go to Data > Text to Columns, and use the wizard to make sure the numbers come out correctly (for the most part, you’ll just need to click Next and Finish without messing with any of the settings).

The text will be converted to numbers and you’re set to go. Note that this only works on one column at a time, so if you have a lot of columns, you might want to go with this final method.

Paste Special

For this method to work, you’ll need to enter the number 1 in a cell (it’s important that this is in number format). Select that cell and copy it. Now, select all of the numbers that you want to convert to number format, and hit Edit > Paste Special. Select “Multiply” from the Operation section and hit OK.

All of the numbers that you selected will be converted to the General format, which recognizes numbers. Any text cells will also be converted to the General format, which shouldn’t cause any problems.

Extracting Numbers or Text from Mixed-Format Cells

Now we get to the hard part: getting numbers out of cells that contain multiple formats of input. If you have a number and a unit (like “7 shovels,” as we have below), you’ll run into this problem. To solve it, we’re going to look at a couple different ways to split cells into numbers and text, letting you work with each individually. The first method is a bit cumbersome, but works very well on small datasets.

LEFT / RIGHT & SEARCH

The main function we’ll be using here is LEFT, which returns the leftmost characters from a cell. As you can see in our dataset above, we have cells with one-, two-, and three-character numbers, so we’ll need to return the leftmost one, two, or three characters from the cells. By combining LEFT with the SEARCH function, we can return everything to the left of the space. Here’s the function:

=LEFT(A1, SEARCH(" ", A1, 1))

This will return everything to the left of the space. Using the fill handle to apply the formula to the rest of the cells, this is what we get (you can see the formula in the function bar at the top of the image):

As you can see, we now have all of the numbers isolated, so they can be manipulated. Want to isolate the text as well? We can use the RIGHT function in the same way:

=RIGHT(A1, LEN(A1)-SEARCH(" ", A1, 1))

This returns x characters from the right side of the cell, where x is the total length of the cell minus the number of characters to the left of the space.

Now the text can be manipulated as well. Want to combine them again? Just use the CONCATENATE function with all of the cells as inputs:

=CONCATENATE(E1, F1)

Obviously, this method works best if you just have numbers and units, and nothing else. If you have other cell formats, you might have to get creative with formulas to get everything to work right. If you have a giant dataset, it’ll be worth the time it takes to get the formula figured out!

Text to Columns

The Text to Columns function is useful for numbers-only columns, but it can also make life easier if you have mixed-format cells. Select the column you want to work with and hit Data > Text to Columns. You can then use the wizard to select a delimiter (a space is usually the best one) and have your column split the way you want it.

If you only have one- and two-digit numbers, the Fixed Width option can be useful too, as it will only split off the first two or three characters of the cell (you can create a number of splits if you’d like, but I’ll save a full explanation of Fixed Width splitting for another article).

If your dataset contains a lot of columns, and you’d rather not use Text to Columns for each of them, you can easily get the same effect using a quick export and import. First, export your spreadsheet as a comma-separated value (CSV) file. Click File > Save As… and save your file as a CSV.

Now, open a new spreadsheet and click File > Import… Select your CSV file and use the import wizard to get your data split into two columns (you’ll use the exact same steps as you did with the Text to Columns wizard). By selecting “Space” in the Delimiters section, you’ll tell Excel to split the data wherever it finds a space, which will isolate the numbers and text.

Click Finish when you’re done, and you’ll get a new spreadsheet that has your columns split into two. Of course, if you have more than one space in a cell, you’ll end up with more than two columns, as you can see here:

Unfortunately, there isn’t a good solution for this using this method; you’ll just have to concatenate the cells back together.

More Complicated Situations

With the strategies above, you should be able to extract numbers or text out of most mixed-format cells that are giving you trouble. Even if they don’t, you can probably combine them with some of the powerful text functions included in Excel to get the characters you’re looking for. However, there are some much more complicated situations that call for more complicated solutions.

For example, I found a forum post where someone wanted to extract the numbers from a string like “45t*&65/”, so that he would end up with “4565.” Another poster gave the following formula as one way to do it:

=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*
 ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

To be completely honest, I have no idea how it works. But according to the forum post, it will take the numbers out of a complicated string of numbers and other characters. The point is that, with enough time, patience, and effort, you can extract numbers and text from just about anything! You just have to find the right resources.

What Strategy Do You Use?

Now that you have a better idea of how to get numbers and letters out of mixed-format cells, we want to know what other strategies you’ve used to do the same thing. Post your formulas, processes, or anything else that might be helpful in the comments so we can all learn from them!