How to Extract Numbers or Text from Excel (2023)

Microsoft ExcelIt's great for working with 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 data more efficiently. We show several options, depending on the format your data is currently in.

Excel numbers formatted as text

This is a common situation, luckily easy to deal with. Sometimes cells containing only numbers are incorrectly labeled or formatted as text, preventing Microsoft Excel from using them in its 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 can also see a green flag in the upper left corner of each cell.

Convert text to numbers in Excel

If you see a green flag in the upper left corner, select one or more cells, click the warning sign, and chooseconvert to number.

Otherwise, select the cell, and in the Ribbon's Number Format menu, choose Defaultnumberoption.

If you need finer options, right click on the highlighted cell and selectcell format, which will open the corresponding menu. Here you can customize the number format and add or remove decimal places, add a 1000 separator, or manage negative numbers.

Of course, you can also use the aforementioned ribbon options or Format Cells to convert numbers to text, or text to currency, time, or any other format you like.

Apply number formatting with Excel special paste

For this method to work, you need to enter a number (any number) into the cell; importantly, this cell is also formatted as a number. Copy that cell. Now, select all the cells that you want to convert to number format, go toHome > Catch > Special Catch, chooseFormatPaste only the cell formatting you copied originally, and clickOK.

This action applies the cell formatting you copied to all selected cells, including text cells.

Extract numbers or text from mixed format cells

Now we come to the hard part: getting numbers from cells that contain multiple input formats. If you have a number and a unit (like our "7 digits" below), you have this problem. To get around this, we'll look at a few different ways to split cells into numbers and text, allowing you to work on each cell individually.

separate numbers from text

If you have many cells containing a combination of numbers and text or multiples of both, it can take a lot of time to manually separate them. To complete the process faster, you can use Microsoft Exceltext to columnFunction.

Select the cells to convert, go toData > Text to ColumnsAnd use the wizard to make sure the cell output is correct. In most cases, you can simply clickNextyesto finalize, but be sure to choose a matching delimiter; in this example, a comma.

If you only have one and two digits thenfixed widthThis option is also useful because it will only split the first two or three characters in the cell. You can even create a series of departments this way.

use:cell format as textNoThey automatically pop up in number format (and vice versa), which means you may still need to convert those cells as above.

Extract numbers or text from delimited string

This approach is a bit cumbersome, but works well on small datasets. We're assuming spaces to separate numbers and text here, although the method works for any other separator as well.

The main function we'll use here is LEFT, which returns the leftmost character in a cell. As you can see in the dataset above, we have cells with numbers of 1, 2 and 3 characters, so we need to return the leftmost 1, 2 or 3 characters of the cell. Combine LEFT withfind function, we can return everything to the left of the space. Here is the function:

=Left(A1,Search("",A1,1))

This returns everything to the left of the space. Apply the formula to the rest of the cells using the fill handle and 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 have now isolated all the numbers so we can manipulate them. Do you also want to isolate text? We can use the RIGHT function in the same way:

=RIGHT(A1, Length(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 spaces.

Now you can also manipulate text. Do you want to combine them again? Just use the CONCATENATE function with all cells as input:

=connect(E1,F1)

Obviously this works best if you just have numbers and units and nothing else. If you have other cell formats, you may need to get creative with the formulas to get everything working. If you have a huge dataset, it's worth the time to figure out the formula!

Extract numbers from one end of a contiguous string

Now, what if you don't have a separator between your numbers and text?

What if you areExtract numbers from the left or right of a string, you can use a variation of the LEFT or RIGHT formula discussed above:

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

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

This will return all numbers to the left or right of the string.

What if you areExtract numbers from the right side of a string, you can also use a two-step process. First, use the MIN function to determine the position of the first number in the string. You can then enter that information into a variation of the CORRECT formula to separate your numbers from your text.

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

=right(A1, only(A1)-B1+1)

use:When using these formulas, keep in mind that you may need to adjust the characters in the columns and the numbers in the cells.

Extract numbers from both ends of a consecutive string

Using the strategies above, you should be able to extract numbers or text from most mixed format cells that give you trouble. Even if they don't, you can combine them with some of the powerful text functions included in Microsoft Excel to get the characters you're looking for. However, there are some more complex situations that require more complex solutions.

For example, I found aforum messageSomeone wants to extract numbers from a string like "45t*&65/", ending with "4565". Another poster gave the following formula as a method:

=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)

Honestly, I don't know how it works. But according to the forum post, it will remove numbers from complex strings of numbers and other characters. The point is, with enough time, patience, and effort, you can extract numbers and text from almost anything! you just needfind the right resources.

After getting more Excel tips? here it ishow to copy formulas in excel.