How to extract a number or text from Excel (2023)

Microsoft Excelis great at working with both numbers and text---but using both in the same cell can run into problems. Fortunately, you can extract numbers or text from cells to work more efficiently with your data. We'll demonstrate different options depending on what format your data is currently in.

Excel numbers formatted as text

This is a common situation and - thankfully - 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 may 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 selectConvert to number.

Otherwise, select the cells and from the Number Format menu on the ribbon, select the default valueNumberchoice.

If you need more detailed options, right-click the highlighted cell(s) and selectFormat cells, which opens the corresponding menu. Here you can customize the number format and add or remove decimals, add a 1000 separator, or manage negative numbers.

Obviously, you can also use the Ribbon or Format Cells options described 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 must enter a number (any number) into a cell; it is important that this cell is also formatted as a number. Copy that cell. Now select all the cells you want to convert to number format, go toHome > Paste > Paste special, selectformatsto paste only the formatting of the cell you copied initially, then clickOK.

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

Extract numbers or text from cells with mixed formatting

Now we come to the hardest part: extracting numbers from cells containing multiple input formats. If you have a number and a unit (such as "7 of spades", as we have below), you will run into this problem. To solve this, let's look at some different ways to split cells into numbers and text so you can work with each one separately.

Separate numbers from text

If you have many cells that contain a combination of numbers and text or multiples of both, separating them manually can be extremely time-consuming. To go through the process faster, you can use Microsoft Excel'sText to Columnsfunction.

Select the cells you want to convert, go toData > Text to Columnsand use the wizard to make sure the cells come out correctly. For the most part, all you have to do is clickFollowingInFinish, but be sure to choose a matching separator; in this example a comma.

If you only have one and two digit numbers, theFixed widthoption can also be useful, as it will only split the first two or three characters of the cell. You can even make some splits that way.

Remark:Cells formatted as text willnot automatically appear with a number format (or vice versa), which means you may still need 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, although the method works for any other separator as well.

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 need to return the leftmost one, two, or three characters from the cells. By combining LEFT with theSEARCH function, we can move everything back to the left of space. This is the function:

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

This will reset everything to the left of the room. Use 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've now isolated all the numbers so we can manipulate them. Do you also want to isolate the text? We can use the RIGHT function in the same way:

=RIGHT(A1, LEN(A1)-LOOKUP(" ", 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. Do you want to combine them again? Just use the CONCATENATE function with all cells as input:

=CONCATENATE(E1, F1)

Obviously, this method works best if you only have numbers and units, and nothing else. If you have different cell formats, you may need to get creative with formulas to make everything work properly. If you have a huge data set, it's worth figuring out the formula!

Grab a number from one end of a continuous string

What if there is no separator between your number and text?

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

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

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

Returns all numbers from the left or right of the string.

If youextract the number on the right side of the string, you can also use a two-step process. First, determine the location of your first digit in the string using the MIN function. You can then enter that information into a variation of the CORRECT formula to separate your numbers from your texts.

=MIN(LOOKUP({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))

=RIGHT(A1, ONLY(A1)-B1+1)

Remark:When using these formulas, keep in mind that you may need to adjust the column characters and cell numbers.

Extract numbers from both ends of a continuous sequence

With the above strategies, you should be able to extract numbers or text from most of the mixed format cells that are giving you trouble. Even if not, you can probably combine them with some powerful text functions in Microsoft 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 oneforum reportwhere 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 a way to do it:

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

To be honest I have no idea how it works. But according to the forum post, it takes the numbers from a complicated series of numbers and other characters. The thing is, with enough time, patience, and effort, you can get numbers and text out of just about anything! You just have tofind the right resources.

Looking for even more Excel tips? Here ishow to copy formulas in excel.