How to extract numbers or text from Excel

Microsoft Excel is great for working with both numbers and text, but if you use both in the same cell, you may have some difficulties. Fortunately, you can extract numbers or text from cells to work with your data more efficiently.

Excel numbers are formatted as text

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

As you can see in the picture below, the cells in column A are formatted as text. You can also see a green flag in the top left corner of each cell.

Convert text to numbers in Excel

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

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

If you need more detailed options, right-click on the highlighted cell (s) and select Format Cells , the corresponding menu will open. Here, you can customize the number format and add or remove decimals, etc.

Apply number format with Excel's Paste Special feature

For this method to work, you will need to 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 a number format, go to Home> Paste> Paste Special , select Formats to paste only the format of the original copied cell, and then click OK.

Extract numbers or text from mixed format cells

Now, we'll get to the harder part: Get numbers out of cells that contain multiple input formats. If there is a number attached to the unit, you will encounter this problem. To solve it, consider a few different ways to split cells into numbers and text.

Separate numbers from text

If you have multiple cells containing a mixture of numbers and text, splitting them manually can take a long time. However, you can use the Text to Columns function of Microsoft Excel.

Select the cells you want to convert, go to Data> Text to Columns and use the wizard to make sure the cells appear correctly. For the most part, you'll only need to click Next and Finish , but make sure you select an appropriate delimiter; In this example, it is a comma.

If only numbers have 1 and 2 digits, the Fixed Width option may also be useful, as it will only separate the first two or three characters of the cell. You can even create some splits that way.

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

Extract a number or text from a delimiter string

This method is a bit complicated, but works great on small data sets. The main function to use here is LEFT, which returns the leftmost characters from a cell. Refer to the article: LEFT function, how to use the function to cut the left string in Excel to know how to do it.

If you want to split the text, you can use the RIGHT function the same way. Refer to the article: RIGHT function, how to use the function to cut the right string in Excel for more details.

Extract numbers from one end of a continuous string

What if there is no separator between numbers and text?

If extracting numbers 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"},"")))) 

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

If extracting numbers from the right of the string, you can also use the two-step process. First, determine the position of the first digit in the string with the MIN function . You can then use that information as a variable for the RIGHT formula, to separate numbers from text.

 =MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")) =RIGHT(A1, LEN(A1)-B1+1) 

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

Extract numbers from both ends of a continuous string

With the above methods, you will be able to extract numbers or text in most mixed-format cells. Additionally, you can combine them with some of the powerful text functions included in Microsoft Excel to get the characters you are looking for. However, there are some complex situations that require more complex solutions.