Excel has a built-in feature to convert text to numbers, but it can be tricky to use when you are trying to select a long list of cells or multiple rows and columns. In the video below I describe how to use keyboard shortcuts to accomplish this very quickly.
Keyboard Shortcuts to Convert Text to Numbers
Here’s a quick guide for the keyboard shortcuts to select the range of cells you want to convert.
- Select any cell that contains the Number Stored as Text error. You will see a small green triangle in the top left corner of the cell.
- Use the following keyboard shortcuts to select the range:
- Ctrl + A – selects the entire contiguous range
- Ctrl + A Twice – selects the entire sheet
- Ctrl + Space bar - selects the entire column
- Shift + Left/Right Arrow – selects multiple columns
- Shift + Space bar - selects the entire row
- Shift + Up/Down Arrow – selects multiple rows
- The error menu drop-down will now be at the top left or right corner of your selected range. Click the Convert to Number option to convert all selected cells to numbers.
I find the built-in feature to be the fastest, but there are other ways to accomplish this task:
- Copy a cell containing a 1 and Paste Special>Multiple on the range you want to convert.
- Use Text to Columns tool on the Data tab (ribbon). This is limited to one column and doesn’t work on rows.
- Functions – the VALUE() function works well, but there are many ways to do this too.
- VBA/Macro – write your own code to automate the process
Do you have a different or easier way? Please leave a comment.