3 Ways To Convert Text To Numbers In Excel

Converting text to numbers, or changing the format from text to number, is one of the common error correction tasks when working with data exported from various databases. In some cases, you can fix it with just one click, but sometimes it’s not that simple. In this article, Buffcom.net will show you 3 effective ways to convert text to numbers in all cases.

How to Identify Numbers Stored as Text

Numbers stored as text are considered an error in Excel’s error-checking rules. This error often occurs when you enter or copy data from a different database or data source, and can cause many issues when calculating, using formulas, and sorting data. There are 2 ways to identify if a number is stored as text or not.

Cells with errors will have a warning tag: Cells with numbers stored as text will have a blue triangle tag, and clicking on it will display a yellow error warning tag like the image below.

Use the ISNUMBER function to check: If the result returns FALSE, then that cell is definitely not in the number format.
Syntax: = ISNUMBER (value)

Where: value is the value or reference of the cell to be checked.

Once you have identified the error, you can determine the most efficient way to convert text to numbers for your data.

3 Ways to Convert Text to Numbers (Text To Number)

Convert Text to Numbers with Error Checking

By default, Excel will warn you if a number is stored in text format. If your Excel file does not display errors in the first identification method, go to the File tab, select Options, select Formulas, scroll down to the Error checking rules group, and check the Numbers formatted as text or preceded by an apostrophe box.

If Excel has already warned you, simply select the entire column or range of numbers with errors, click on the yellow warning tag, and select Convert to Number.

This is the quickest and easiest way to convert text to numbers. The text will be converted to the existing number format of that column or data range.

Convert Text to Numbers Using Paste Special Tool

This is a method you can use to convert text to numbers when copying data from one file to another and need to change the format to avoid calculation errors.

Select the entire column or range of data to be corrected:

• Select Copy in the Home tab or press the Ctrl C shortcut.
• Move to the location where you want to paste the data.
• Home tab, Clipboard group, expand the Paste options, select Paste Special… to open the Paste Special dialog box.
  paste special to convert text to numbers

• In the Operation group, select Add.
• Select OK.

The principle of this method is that when pasting new data into a selected position, Excel will perform calculations such as addition, subtraction, multiplication, and division with the copied data regardless of the storage format. Therefore, the original data is converted back to its original format as a number. You should choose the Add or Subtract operation to avoid changing the numerical value.

Converting Text to Numbers with the VALUE Function

If the above two methods of converting text to numbers still do not meet your needs, you can refer to this third method. The VALUE function helps convert a text string representing a number to a numeric value, and you can combine it with other functions to ensure that the function does not result in errors.

Syntax: =VALUE(text)

Where: text is a required value, which is the text enclosed in double quotes or a reference to the cell containing the text.

Note: You need to write the function in another column to create a numeric value, then copy and paste it back into your data column.

In conclusion

Those are the three most effective ways to convert text to numbers that you can flexibly modify to suit your data. In some guides, you can still use the feature to convert data formats from text to numbers using the format cells option. However, this method doesn’t always work, so you should still expand your knowledge and remember the methods above.

Additionally, if your work is closely related to office software but the software doesn’t meet your needs, you can contact Buffcom.net for support!