How to convert an entire column to a number in Excel

When you open a .txt text document in an Excel file, some numeric data is displayed in text format. To convert numeric data not in the entire document, but only in a specific column, there are several ways that we will consider in this article.

Issues Caused by Incorrect Number Format

The presence of an error related to the fact that numbers are displayed as text is indicated by a small green triangle in the upper left corner of the cell. In this case, calculations with these values ​​are not performed, and if you try to sort the numbers, for example, in ascending order, the sorting will be violated.

To correct this error, you must select all cells in which the wrong format is set. In this case, the icon “!” (exclamation point), when you click on which Excel itself will indicate the presence of an error, namely “Number saved as text.”

When you click on the “Convert to numbers” option, the format of all numeric values ​​​​will change with them, you can carry out any calculations. In this case, the green triangles in the cells will disappear.

Changing the cell format

Numeric values ​​in an Excel spreadsheet, expressed in text format, do not allow you to work with formulas and do the necessary calculations. Cells can also change the established format as a result of transferring the file to another computer or when opening a document in a different version of Excel. To correct this error, you must perform the following sequence of actions:

1. First of all, you need to select the column in which the changes will be made or the area of ​​cells.

2. Next, go to the “Data” tab and in the “Working with data” category, activate the “Text by columns” parameter. A dialog box will open – “Wizard of text distribution by columns”.

3. The settings consist of three steps, in accordance with which the column parameters are set – the format of the initial data with a separator or fixed width, the choice of a separator and the format of the column data. In this case, we are satisfied with the standard settings, so we click “Finish” and the column values ​​​​will go into a numeric format.
4. When you press the key combination “Ctrl + 1”, the “Format Cells” window will appear. Here, select the “Numeric” option and click “OK”.

If the cells display formulas instead of the results of calculations, then you need to turn off the “Display formulas” option in the “Formulas” tab.

Format conversion via formulas

You can convert numeric values ​​from text to numeric format using the special VALUE formula.

1. In this case, you need to create a new column to the right of the values ​​that will be converted to another format.
2. In the first cell of the new column, enter the formula «=SIGNIFICANT(D5)”. In parentheses, enter the address of the cell.
3. After applying the formula in the first cell, you should stretch its effect to the entire length of the column by clicking the mouse cursor on the lower right corner of the cell and dragging it down.

4. The converted values ​​are copied and transferred to the column with the original data. Select the column with the new values ​​and press the key combination on the keyboard “Ctrl + C”. Thus, the values ​​are saved in the clipboard. Next, go to the first cell of the column with the original values ​​and, by clicking on the arrow under the “Insert” parameter on the “Main” tab, select the “Insert values” category.

We use special inserts

An equally simple and effective way to convert numbers from text to number format is to use Excel special pastes. So, to find out in what format the number is currently displayed, it is enough to look at the tool block on the “Main” tab when activating the cell. There is an option here that displays the cell format. With standard settings – the “General” format. Clicking on the arrow on the left will bring up a menu to select other formats.

To carry out the conversion, put the number 3 in one of the cells, which will remain in the “General” format. You need to copy the specified cell and move to another area. When you click on the arrow under the “Clipboard” parameter, select the “Paste Special” criterion. A window will appear with a specific set of options. In the “Operation” settings block, you must check the box next to “Multiply”.

Disabling error checking

In some cases, it is required to enter numerical values ​​in the form of text. To prevent Excel from displaying invalid format errors as green triangles in a cell, you must turn off the validation feature.

1. With the Excel file open, go to the “File” tab.
2. In the left panel, go to the “Settings” category.

3. In the window that appears, go to the settings called “Formulas”.

4. In the “Error Search Rules” command set, you need to make sure that the activation box is checked next to the “Numbers formatted as text or with a preceding apostrophe” parameter.

If, on the contrary, errors are not displayed in the Excel document, then the checkmark next to the above parameter must be unchecked and the selection activated by pressing the OK button.

Conclusion

Thanks to this article, you can easily learn how to convert a column to a number using several convenient methods. Each user will be able to choose for himself the best option to perform the tasks.