When working with MS Excel, you may have often encountered an error when trying to perform arithmetic operations on numbers. For some reason, the program does not perform calculations and displays a message stating that such an operation should be performed on numeric elements. Why such problems happen and what to do with them, read below.
Number in text format: causes and problems
An error occurs if for some reason the program does not perceive numbers as numbers, considering them to be text. This can happen when copying data from another source - another program, web page, etc. The problem may also occur when importing information from another file or database.
Pay attention to how the field with the number looks. If in the corner of the field you see a small green triangle, this means that the numerical information in it is entered as text. The problem described above when trying to make calculations also speaks about this.
Another problem when storing numbers as strings arises when trying to sort them. Take for example a series of numbers: 10, 211, 15, 1021, 26, 100, 128. Sorting them is not difficult, and the program easily copes with it. But, if these numbers are written in a string format, the sorting will radically differ from the usual and necessary. This difference is shown in the table:
Sort in number format | Sort in text format |
ten | ten |
15 | one hundred |
26 | 1021 |
one hundred | 128 |
128 | 15 |
211 | 211 |
1021 | 26 |
What to do in this case and how to convert text to number in Excel?
Bug fix
As mentioned above, a green mark in the corner of the field indicates the problem. Such marking can be on unit fields, the whole range, column or record. The program independently determines the discrepancy between the format of the cell and the value that is entered in it, and signals us about it. Now you only need to use this signal correctly.
Select a field or a series of fields in which a problem situation is observed. Several disparate cells can be selected using the Ctrl key, a column or record is selected by clicking on the header. To select the entire sheet, you need to click on the connection of the record headers and the graph in the upper left corner of the Excel page.
An icon will appear next to the selected field range - a button with an exclamation mark indicating an error. When you click on the button, a menu appears, the first item of which shows what kind of problem is in the selected area of the sheet ("The number is saved as text"). Click Convert To Number. The green error indicators will disappear immediately, and the necessary actions can be performed on the numbers.
Using custom insert
Another way to help convert text to number in Excel is to use a special insert. In one of the free cells, enter the number 1, having previously set the format as general: on the "Home" tab of the program, the "Number" toolbar shows the format of the selected field.
Copy unit. To do this, right-click on it and select the "Copy" command from the menu. The second method is a button on the clipboard toolbar. And the third one is the keyboard shortcut Ctrl + C.
Select the range of numbers in text format that you intend to convert to numerical. In the same clipboard panel, click Paste / Paste Special. A window for entering parameters will open. In it, mark the point "multiply" of the block "Operation" and click "OK" - Excel text is converted to a number.
The same operation can be performed using the context menu. Having selected the desired range, right-click, select “Paste Special” / “Paste Special”. The same window will open.
Using formulas and macros
Another way to convert text to number in Excel is with a formula. Insert an empty column next to the problem cell or graph. Enter the formula in the upper field of the new column. To do this, write "=". The program perceives this sign as the intention to add a calculation and suggests choosing a function. Click the fx function icon under the toolbar.
In the window that opens, in the search bar, enter the text "significant" and find the function "VALUE". In the function parameters, specify the desired cell and click "OK". In the cell next to the desired cell, you will get a number instead of text. To copy a formula to all fields of a column, click with the mouse in the lower right corner of the cell containing it, having received the cursor in the form of an image of a cross, hold it and drag down the entire length. The formula appears throughout the graph.
Those who are familiar with programming can help convert text to Excel VBA numbers. This built-in language offers data type transformation commands. For integers, the CInt operation is suitable; for fractional numbers, CDbl. Below is an example of using VBA for our case:
Sub TestModule()
Cells(1, 12) = CInt(Cells(1, 11))
End Sub
Inverse problem
Sometimes the inverse task is to convert a number into text in Excel in words. This problem is as solvable, although not as simple as the previous one. There are special add-ons that are added to the program. Then, functions are called to change the display of numbers.
Another way is to use formulas. It is much more cumbersome, since it involves the creation of user directories - named ranges in the name manager. These names are then used in the formula based on the SUBSTITUTE function.
Whichever way you choose, in Excel to convert text to number, applying it in practice several times, you will adapt to do this quickly and efficiently, getting the desired result.