It’s common to find numbers stored as text in Excel. This leads to incorrect calculations when you use these cells in Excel functions such as SUM and AVERAGE (as these functions ignore cells that have text values in it). In such cases, you need to convert cells that contain numbers as text back to numbers. Show
Now before we move forward, let’s first look at a few reasons why you may end up with a workbook that has numbers stored as text.
Now, let’s see how to tackle such cases. Convert Text to Numbers in ExcelIn this tutorial, you’ll learn how to convert text to numbers in Excel. The method you need to use depends on how the number has been converted into text. Here are the ones that are covered in this tutorial.
Convert Text to Numbers Using ‘Convert to Number’ OptionWhen an apostrophe is added to a number, it changes the number format to text format. In such cases, you’ll notice that there is a green triangle at the top left part of the cell. In this case, you can easily convert numbers to text by following these steps:
This would instantly convert all the numbers stored as text back to numbers. You would notice that the numbers get aligned to the right after the conversion (while these were aligned to the left when stored as text). Convert Text to Numbers by Changing Cell FormatWhen the numbers are formatted as text, you can easily convert it back to numbers by changing the format of the cells. Here are the steps:
This would instantly change the format of the selected cells to General and the numbers would get aligned to the right. If you want, you can select any of the other formats (such as Number, Currency, Accounting) which will also lead to the value in cells being considered as numbers. Also read: How to Convert Serial Numbers to Dates in Excel Convert Text to Numbers Using Paste Special OptionTo convert text to numbers using Paste Special option: Convert Text to Numbers Using Text to ColumnThis method is suitable in cases where you have the data in a single column. Here are the steps:
While you may still find the resulting cells to be in the text format, and the numbers still aligned to the left, now it would work in functions such as SUM and AVERAGE. Convert Text to Numbers Using the VALUE FunctionYou can use a combination of VALUE, TRIM and CLEAN function to convert text to numbers.
Suppose you want convert cell A1 from text to numbers, here is the formula: =VALUE(TRIM(CLEAN(A1))) If you want to apply this to other cells as well, you can copy and use the formula. Finally, you can convert the formula to value using paste special. You May Also Like the Following Excel Tutorials:
How do I convert letters to numbers?The Letter-to-Number Cipher (or Number-to-Letter Cipher or numbered alphabet) consists in replacing each letter by its position in the alphabet , for example A=1, B=2, Z=26, hence its over name A1Z26 .
How do you change ABC to 123 in Excel?My column headings are labeled with numbers instead of letters. On the Excel menu, click Preferences.. Under Authoring, click General .. Clear the Use R1C1 reference style check box. The column headings now show A, B, and C, instead of 1, 2, 3, and so on.. How do I convert text to numbers in Excel using keyboard?Select the cells you want to convert to numbers, right-click, and then click Paste Special. Alternatively, press the Ctrl + Alt + V shortcut. In the Paste Special dialog box, select Values in the Paste section and Add in the Operation section. Click OK.
Why can't I convert text to numbers in Excel?On the Tools menu, click Options. In the Options dialog box, click the Error Checking tab. In the Settings section, click to select the Enable background error checking check box. In the Rules section, make sure the Number stored as text rule is selected, and then click OK.
|