Often working with Excel spreadsheets it will have happened more than once to see the added numbers as if they were simple text, with the result that the formulas or calculations integrated within a table no longer work correctly.This is a very common problem in Excel, especially when we copy numbers from Word, Web pages or any other source: once pasted they do not become numbers that can be managed by Excel, remaining in text format.
In this guide we will show you quickly how to convert text to numbers on excel, so as to make all the formulas we have created for the table in use work, without generating any error windows.
How to convert text to numbers in Excel
Microsoft Excel is a really powerful tool and allows you to quickly resolve any type of error, even if we have entered numbers as text instead of in a format compatible with the spreadsheet.Check if the numbers are in text format
First we can quickly check if the selection of numbers we have highlighted are in text format by pressing at the top of the menu Home and checking the drop-down menu in the Numbers section.
If the entry is present Text and we note that the numbers are positioned on the left edge of each cell, we are dealing with numbers expressed in text format, therefore not usable in formulas or graphs, often causing an error window when we try to add them or to expand them into a mathematical formula.
Within the tables we can carry out the same check by checking if there is a triangle in the upper left corner of the cell with the suspect number: in this case we are dealing with
a number expressed in text format, therefore not usable for Excel operations.
Convert text to numbers
Once we have learned the origin of the problem, we can convert the selected text, a series of cells in a table or even a single cell into numbers using the conversion tools provided by Excel itself.The first method involves highlighting the cell or cells to be converted, press the drop-down menu seen a little while ago (in Home -> Numbers) and finally select the item Numeri.
The numbers will be converted to an Excel-compatible format, also highlighted by the shifting of the numbers to the right edge of each cell and the appearance of decimal numbers. If we do not want the decimal numbers expressed for the highlighted values, just press the icon Decrease decimals (present in Home -> Numbers).
If the numbers in text format are already inserted in a table, a small yellow diamond will appear with the exclamation point in correspondence with the error; pressing on it we will be able to quickly convert the text into numbers by pressing on Convert to number.
Do we have a whole column full of text to convert? In this case, just double-click on the letter of the column, press at the top of the menu Data and finally click on the icon Text in columns.
A wizard will open where you can indicate the type of data to import, choose the delimiters and finally the format that the generated column should have, complete with a preview of how it will appear once pressed on the button end.
In this way we will be able to quickly convert entire columns of data mistakenly entered into text format, restoring the operation of any formulas present within them.
Another very effective method to convert text to numbers in Excel is to use the function VALUE, very useful for converting a cell or an entire column of numbers expressed in text format on the fly.
To use this function, let's go to a free cell (in which to move the numbers generated by the text) and type the command
= VALUE (A1)
We obviously replace A1 with the letter and number of the cell to convert.
Pressing on Enter we will obtain a new cell with the same value but in a compatible numeric format; to convert the other numbers in the column just select the lower right corner of the first converted cell and scroll down until the new column coincides with the old one.
How to correctly import numbers from text format
When we paste data from Word or other text documents Excel should automatically convert to text; if this does not happen, we must ensure that the copy and paste is made safe, so that only the numerical values useful for the spreadsheet are taken.Before copying anything, press the right button on the letter of the column in which we will insert the data, select the item Celle format and, in the card Number, make sure the item is selected General.
We press on OK; now we are ready to import the numbers! We copy the text with the numeric component, select the cells to be filled, press the right button on them and use as an option to paste the item Values.
In this way the numbers we had copied will be inserted in the correct number format, compatible with all formulas and mathematical operations supported by Excel.
How to convert text to numbers in LibreOffice
If we use the free version of Office on our computer (i.e. LibreOffice), we can convert the text into numbers by selecting the cell or cells to be converted and pressing the button Format as Number.
Alternatively we can convert an entire column of numbers by double clicking on the letter that identifies the column, pressing on the menu Data and clicking on the item Text in columns.
Conclusions
Microsoft Excel is one of the most requested programs in the workplace and knowing how to use it well could be worth much more than any resume and degree! If we find ourselves with a spreadsheet full of text we can quickly fix it by adopting one of the methods seen in the guide above.If we are studying to become true Excel experts we recommend that you read our guides Getting good with Excel on spreadsheets (also with LibreOffice and Google Drive) e How to use Excel Formulas and Functions.
We don't know how to make a table in Excel? We can fix it quickly by reading our guide How to make a table in Excel.
