When you have numbers stored as texts in excel, it can lead to incorrect or unexpected calculations and error results. To avoid making such mistakes in your workbook, you will need to convert the texts into numbers. For any excel user, knowing the different ways of how to convert unwanted texts into numbers can come in handy.
Below, we guide you on the different ways you can identify the texts within your workbook and convert them to numbers.
Convert to number option
Most times, we might mistakenly use an apostrophe instead of a comma when typing numbers in excel. When this is done, excel equates such an entry as a text format. You can use the 'Convert to number' option to convert the text format to a number format in your workbook.
1. Start by selecting all the cells that you want to convert from text format to number format.
2. On the top right of your selection, click on the yellow diamond shape icon.
3. In the pop-up menu that appears, select the option 'Convert to Number.' Doing so, all your texts will be converted to number formats and aligned to the right of the cells.
4. Sometimes, the yellow diamond icon may not be displayed. In such a case, you will use the text to column method stated below.
Text to Column method
You can also use this method to convert your text to numbers when you have data in a single column.
1. First, select all the cells that contain the data you want to convert.
2. Go to the Data tab on the main menu.
3. Under the Data Tools group, click on the option 'Text to Columns' to display a wizard box
4. In the wizard, check the Delimited option and click on the options button, 'Next.' It is always a default choice in Excel.
5. Check the Tab box as the delimiter and click 'Next.'
6. Under the field Column data format, select the option, General.
7. Next, it is optional to specify the destination you want your results displayed or keep the currently selected destination.
8. Click Finish. Your data will be converted to numerical values.
The Paste Special option
You can also convert your text to numbers using the excel option Paste special.
1. In an empty cell within your worksheet, enter 1.
2. Copy the cell that contains your entry.
3. Next, select the cells that you want to convert from text to number formats.
Afterward, right-click and select Paste Special option to display a dialog box.
5. Here, go to the Operation category and select the option 'Multiply.'
6. Click OK.
Changing the Cell Format
Another method of converting texts to numbers is by changing the format of your cell.
1. Select all your cells that contain the text you want to be converted to numbers.
2. Go to the Home tab in the main menu ribbon.
3. Click on Number to display the Number Format drop-down list.
4. Select the option 'General.' It will change the format to a text one.