One of the most common annoyances people name in Excel is the formatting of numbers and text in cells. It is especially annoying when numbers are inadvertently entered into a table in text format.
When this happens, calculations and various formulas do not work correctly or may not work at all.
In this article, you will learn how to determine when a column or row of numbers is actually formatted as text, and how to convert text to numbers so that they work again in formulas and calculations. This is one of those basic Excel tips that everyone should know.
Is text cellular data or numbers?
There are several ways to find out if a number or set of numbers in a column or row is formatted as text in Excel.
The easiest way is to select a cell, select the main menu and, in the Number group on the ribbon, mark the number format displayed in the drop-down list.
If the dropdown shows “text”, you know that the cell is formatted as text format. If you want to perform numeric calculations on a cell using Excel formulas, you need to convert it first.
– /
If someone entered numbers in text format using an apostrophe in a cell, you will see a small green triangle indicating that the value was entered as text.
Note. Adding an apostrophe to a cell results in text formatting of the cell.
If you find, using any of the above approaches, that numeric data is being entered into an Excel spreadsheet in text format, you can use any of the methods below to convert that text to numbers.
1. Convert to a number
If you need to convert data entered in Excel with an apostrophe, you can easily convert it back to number format using the Convert to Number option.
1. First, select the cells that you want to convert back to number format. A yellow diamond with an exclamation mark in the middle will appear next to the selection.
2. Select this symbol. Select Convert to Number from the drop-down list.
This will update all the text numbers you selected to the general numeric data format.
You will know that it worked when all the numbers in the selection switched from left-aligned to right-aligned in cells.
2. Use text in the column
Another easy way to convert text to numbers in Excel is to convert an entire column of values ??at once. You can do this with the Text to Column function.
1. Select the entire column of data that you want to convert from text to numbers.
2. Select Data from the menu, and then select Text to Columns from the Data Tools section of the Ribbon.
3. In the wizard, leave the default delimited value selected and click Next.
4. On the next page of the wizard, leave the default tab selected and click Next again.
5. Finally, on the last page of the wizard, make sure General is selected in the Column Data Format section. For the Destination field, you can either select a new column where you want to place the numeric data, or just leave the currently selected column as it is. Select Finish.
Now all of your data will be converted to numeric values ??that you can use in Excel formulas and calculations.
Note. You will notice that the actual cell formatting does not change from Text to General, although the values ??themselves can now be used as numbers. However, if you set the output column to a new column, you will notice that the formatting of the new column is set to General. This is purely a cosmetic issue and does not affect the behavior of numbers in a Text formatted column.
3. Change the cell format
The easiest and fastest way to convert text to numbers in Excel is to simply change the cell formatting in the main menu.
To do this:
1. Select all the cells you want to convert. You can select the entire column (no header) if you want to convert all cells in the column.
2. Select the main menu and in the Number group on the ribbon select the drop-down list with text in it.
3. You will see a list of formats to choose from. Select General to convert to number format. Or you can select Number, Currency, Accounting, or Percentage if you want these specific number formats to apply to your number data.
4. Use paste values
If you need to move text cells containing numbers to a new cell or column, you can use the Paste Special feature.
1. Select the blank cell group where you want to place the numeric output. Select Format Cells from the pop-up menu.
2. In the window that opens, make sure that “General” is selected as the number format, and click “OK”.
3. Select the entire column of cells you want to convert from text to numbers, right-click and select Copy.
4. Select the first cell in the blank column that you have formatted, right-click the cell and choose Insert Values. You will see that all the numbers in text format are inserted in the general number format.
This works because when you select Paste Values, only the values ??from the original cell are pasted, not the original cell formatting. Instead, it uses the destination cell formatting that you configured in the first part of this process.
5. Using the VALUE function
Excel has a special function that converts a number formatted as text to a numeric value. This is the VALUE function.
To use this function, select the cell where you want to place the converted number and enter:
= VALUE (G2)
Replace “G2” above with the cell with the number you want to convert. If you are converting an entire column of numbers, start at the first cell only.
Press Enter and you will see the text format number converted to general format number.
Then you can fill the rest of the blank column to the end of that column, and the VALUE formula will also convert the rest of the cells in the original column.
After using any of these number reformatting options, you may need to update the cell data after applying the new formatting.
As you can see, Excel has several ways to convert text to numbers. The option you choose just depends on where you are trying to place the pin. It also depends on whether you prefer to use copy and paste, Excel formulas, or menu options.
Ultimately, each of these options produces the same end result.
–