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

Microsoft Excelworks great with numbers and text, but if you use both in the same cell, you might run into some difficulties. Fortunately, you can extract numbers or text from cells to work with data more efficiently. We present you with several options, depending on the format your data is currently in.

Excel numbers formatted as text

This is a common situation and --- fortunately --- very easy to resolve. Sometimes number-only cells are highlighted or incorrectly formatted as text, preventing Excel from using them in functions.

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

Convert text to number in Excel

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

If not, select the cells and choose Default from the Number Format menu on the ribbonNumberselection.

If you need more specific options, right-click on the highlighted cells and selectcell formattingwhich will open the appropriate 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 strip or cell format options described above to convert a number to text or text to currency, time, or any other format.

Apply number formatting using Excel's special paste function

For this method to work, you must enter a number (any number) in the cell. It is important that this cell is also formatted as a number. Copy this cell. Now select all the cells you want to convert to number format, go toHome > Catch > Catch Special, selectformsto paste only the cell formatting you copied first, and then clickOkay.

This function applies copied cell formatting to all selected cells, including text cells.

Extract numbers or text from mixed format cells

Now we come to the hardest part: getting cell numbers with multiple input formats. If you have a number and a unit (eg "7 spades" as below), you will run into this problem. To solve this problem, we'll look at several different ways to split cells into numbers and text, allowing you to work with each one individually.

Separate numbers from text

If you have many cells that contain a combination of numbers and text, or multiples of them, it can take a long time to separate them manually. To make the process faster, you can use Microsoft Exceltext in columnsmode.

Select the cells you want to convert, go toData > Text in Columnsand use the wizard to make sure the cells come out correctly. In most cases, just clickNextIFlapbut be sure to select a matching delimiter. comma in this example.

If you only have one and two digit numbers, thenconstant widthThe option can also be useful as it will only separate the first two or three characters of a cell. You can even create multiple splits this way.

Use:Cells formatted according to textNOwill automatically appear formatted as a number (or vice versa), which means you may need to convert these cells as described above.

Extract number or text from a delimited string

This method is a bit complicated, but works great on small datasets. Here we assume that a space separates the number from the text, although this method works for any other delimiter as well.

The main function we'll use here is LEFT, which returns the leftmost characters in a cell. As you can see in our dataset above, we have cells with one, two, and three character numbers, so we will need to return the left one, two, or three characters of the cells. Combination LEFT withlooking for function, we can return everything to the left of the space. Here is the function:

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

This will return everything to the left of the part. Using the fill handle to apply the formula to the remaining cells, we get this (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 too? We can use the RIGHT function in the same way:

=RIGHT(A1,DBT(A1)-LOOKUP(" ",A1,1))

This will return X characters to the right 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 reconnect them? Just use the CONCATENATE function with all cells as input:

=CONCATENATE(E1, F1)

Of course, this method works best if you only have numbers and units and nothing else. If you have other cell formats, you may need to get creative with the formulas to get things working properly. If you have a huge data set, it's worth considering a formula!

Extract a number from one end of a continuous string

What if there is no delimiter separating your number from the text?

if you areextract a number from the left or right side of a string, you can use the LEFT or RIGHT variant of the formula discussed above:

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

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

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

if you areextract the number from the right side of the string, you can also use the two-step process. First, determine the position of the first digit in the string using the MIN function. You can then enter this information into a variation of the RIGHT type to separate numbers from texts.

=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, be aware that you may need to adjust column characters and cell numbers.

Extract numbers from both ends of a continuous string

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

I found for exampleforum messagewhere someone wanted to extract numbers from a string like "45t*&65/" to get "4565". Another poster gave the following formula as a way to do it:

=SUMPRODUCT(AVERAGE(0&A1LARGE(INDEX(ISNUM(--AVERAGE(A1,ROW($1:25$),1)))*
ROW($1: $25), 0), ROW($1: $25))+1,1)*10^ ROW($1: $25)/10)

To be perfectly honest, I have no idea how it works. But according to a forum post, it will remove numbers from a complex string of numbers and other characters. The thing is, with enough time, patience and effort, you can extract numbers and messages from almost anything! You just have tofind the right resources.

After some extra Excel tips? Herehow to copy formulas in excel.