When this happens, calculations and different formulas don’t work quite right, or may not work at all. In this article you’ll learn how to identify when a column or row of numbers are actually formatted as text, and how to convert text to numbers so that they’ll work in formulas and calculations again. This is one of those basic Excel tips everyone should know.
Is Cell Data Text Or Numbers?
Is Cell Data Text Or Numbers?
There are several ways you can see if a number or set of numbers in a column or row is formatted as text in Excel. The easiest way is to select the cell, select the Home menu, and under the Number group in the ribbon, note the number format displayed in the dropdown box. If the dropdown box displays “text”, you know the cell is formatted as text format. If you want to perform numerical calculations on the cell using Excel formulas, you’ll need to convert it first. In the case where someone has entered numbers in text format using the apostrophe in the cell, you’ll see a small green triangle indicating the value has been entered as text. Note: Preceding a cell entry with an apostrophe forces the cell formatting to text-based. If you’ve discovered, using either of the approaches above, that the numerical data is entered into the Excel sheet in text format, you can use any of the methods below to convert that text to numbers.
1. Convert To Number
1. Convert To Number
If you need to convert data that’s been entered into Excel with an apostrophe, you can easily convert it back to number format using the Convert to Number option.
2. Using Text to Column
2. Using Text to Column
Another easy way to convert text to numbers in Excel is by converting over an entire column of values at once. You can do this using the Text to Column feature.
3. Changing Cell Format
3. Changing Cell Format
The easiest and fastest way to convert text to numbers in Excel is simply changing the cell formatting from the Home menu. To do this:
4. Using Paste Values
4. Using Paste Values
If you need to move text cells that contain numbers into a new cell or column, you can use the Paste Special feature.
5. Using The VALUE Function
5. Using The VALUE Function
There is a special function in Excel that’ll convert a number formatted as text into a numeric value. This is the VALUE function. To use this function, select the cell where you want the converted number to go and type: =VALUE(G2) Replace “G2” above with the cell that has the number you want to convert. If you’re converting an entire column of numbers, start with the first cell only. Press Enter and you’ll see that the text-formatted number has been converted to a General-format number. You can then fill the rest of the empty column to the bottom of that column and the VALUE formula will convert the rest of the cells in the original column as well. After using any of these options for reformatting your numbers, you may need to refresh cell data after applying the new formatting. As you can see, there are a number of ways to convert text to numbers in Excel. The option you choose just depends on where you’re trying to place the output. It also depends whether you prefer using copy and paste, Excel formulas, or menu options. Ultimately, each of these choices provides you with the same end result.
