How to Extract a Number or Text From Excel

Microsoft 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.

Unlock the FREE “Essential Excel Formulas” cheat sheet now!

This will sign you up to our newsletter

Enter your Email Unlock Read our privacy policy

Excel Numbers Formatted as Text

This is a common situation, and—fortunately—very easy to deal with. Sometimes, cells that contain only numbers are incorrectly labeled or formatted as text, preventing Microsoft Excel from using them in operations.

You can see in the image below that the cells in column A are formatted as text, as indicated by the number format box. You might also see a green flag in the top left corner of each cell.

Convert Text to Number in Excel

If you see the green flag in the top left corner, select one or more cells, click the warning sign, and select Convert to Number.

Otherwise, select the cells and, in the Number Format menu in the Ribbon, select the default Number option.

If you need more granular options, right-click the highlighted cell/s and select Format Cells, which will open the respective menu. Here, you can customize the number format and add or remove decimals, add a 1,000 separator, or manage negative numbers.

Obviously, you can also use the Ribbon or Format Cells options outlined above to convert a number to text, or text to currency, time, or any other format you desire.

Apply Number Formatting With Excel’s Paste Special

For this method to work, you’ll need to enter a number (any number) into a cell; it’s important that this cell is also formatted as a number. Copy that cell. Now, select all the cells that you want to convert to the number format, go to Home > Paste > Paste Special, select Formats to paste only the formatting of the cell you copied initially, then click OK.

This operation applies the format of the cell you copied to all selected cells, even text cells.

Extract 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.

Separate Numbers From Text

If you have a lot of cells that contain a mix of numbers and text or multiples of both, separating them manually might take a monumental amount of time. To get through the process faster, you can use Microsoft Excel’s Text to Columns function.

Select the cells that you want to convert, go to Data > Text to Columns, and use the wizard to make sure the cells come out correctly. For the most part, you’ll just need to click Next and Finish, but do make sure you pick a matching delimiter; in this example, a comma.

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 even create a number of splits that way.

Note: Cells formatted as text will not automatically emerge with a number formatting (or vice versa), meaning you might still have to convert these cells as described above.

Extract a Number or Text From a Delimited String

This method is a bit cumbersome, but works very well on small datasets. What we assume here is that a space separates the number and text, though the method also works for any other delimiter.

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 the numbers isolated, so we can manipulate them. 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 you can also manipulate the text. Want to combine them again? Just use the CONCATENATE function with all 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!

Extract a Number From One End of a Continuous String

Now what if there’s no delimiter separating your number and text?

If you’re extracting the number from the left or right of the string, you can use a variation of the LEFT or RIGHT formula discussed above:

=LEFT(A1,SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"0","1","2","3","4","5","6","7","8","9"},""))))

=RIGHT(A1,SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"0","1","2","3","4","5","6","7","8","9"},"")))) Read more

Why You Shouldn’t Learn to Code With Codecademy

Codecademy is a web app designed to teach new developers the building blocks of coding. It’s wildly popular but does have a lot of things it can do better.

Since 2011 they’ve taught millions how to code for free, and have launched the careers of thousands of developers. At the same time, their product and teaching methods leave much to be desired. Read more

What Are Popsockets? 6 Reasons Why You Should Consider Buying One

Have you seen one of those weird circular discs slapped on the back of someone’s phone? Chances are, that’s a PopSocket. They’re hugely popular right now, especially with the younger crowd.

But what is a PopSocket? And more importantly, are PopSockets removable? We’re going to dive right in and answer all of your burning questions. Read more

20 Online Shopping Sites With Free International Shipping

The rise of online shopping has made international shipping the conveyor belt of the world. You can order a didgeridoo from Australia and it’ll turn up on your doorstep in Iceland within the week. But, because international shipping doesn’t come cheap it’ll probably cost you a fortune. Read more