If you want to convert text to numbers in Microsoft Excel then you are in the right place. The YouTube video should provide you with all the information you need, and there is also a full description below the video.
Link to Download the Example Excel File
Click on the Excel icon below to download the example Excel spreadsheet used in the video above.
Quick and Easy Solution — Convert Text to Numbers
- Click on one of the cells with the issue (i.e., it contains text).
- Click on the yellow error message to the left (i.e., the yellow square with an exclamation mark in the middle).
- In the drop-down menu that shows up after completing the last step, select “Convert to Number.”
Better Solution for Large Data Sets
If you have many cells that you want to convert from text into number format, this solution will likely be a better option for you.
1. In an empty space in your Excel spreadsheet, add zero (i.e., 0) to one of the cells that contains data in text format (see below for an example where a 0 is added to cell C6).
2. Select (e.g., click on) the cell you used for step 1 above. Then double-click on the very small green square at the bottom-right part of the cell. Alternatively, you can click on this tiny green square and drag it down.
How to Remove Spaces in Cells
This section goes over how to remove unwanted spaces in cells, which are often present when numbers are formatted as text.
1. Select the cells that have the formatting issue.
2. Open the Find and Replace dialog box. You can access this by using the shortcut CTRL+H (or Command-H on a Mac). You can also access this dialog box by going to the Home tab on the Excel ribbon, and then on the far right under “Find & Select,” choose “Replace…”
3. In the Find and Replace dialog box, type one space in the box next to where it says “Find what,” and then click on “Replace All.” This will remove all of the spaces in the cells you selected in step 1 above.
Verifying Your Cells Have Been Converted from Text to Number Format
Here is how you can determine if your cells are now in number format or if they are still in text format.
1. Open the Format Cells dialog box. You can access this dialog box by using the shortcut CTRL+1. You can also access it by going to the Home tab on the Excel ribbon, and then clicking on the very small icon at the bottom right part of the Font section.
2. Check to see the current format of your data on the “Number” tab right below where it says “Category.” Note that the format for “Accounting” is very similar to “Number.” If you would like to change the type of format or number of decimal places, you can make those adjustments in this dialog box.
I hope you found this to a helpful overview of how to convert text to number format in Microsoft Excel. Please leave a comment below and like us on Facebook so your friends can benefit from these resources as well.