Quickly convert numbers stored as text [tip]

Here is a quick tip to start the week.

Often, we end up with a situation where a bunch of numbers are stored as text.

In such cases, Excel displays a warning indicator at the top-left corner of the cell. If you click on warning symbol next to the cell, Excel shows a menu offering choices to treat the error.

Converting numbers stored as text back to numbers

Tip #1: Error correction

One easy and quick way to convert all the text-numbers to numbers is,

1. Select all the cells
2. Click on warning indicator next to top-most cell
3. Choose Convert to number option
4. Done

Tip#2: Paste Special Convert

Sometimes such text-numbers may be scattered across the worksheet, thus making selection of cells a pain.

In such cases, follow this process (see demo below)

1. In a blank cell, just type 1
2. Copy this blank cell.
3. Select all the cells that have text-numbers.
4. Press CTRL+ALT+V to launch Paste Special box.
5. Choose Multiply from operation area.
6. Click ok & Done!

Enjoy your numbers.

Bonus tip: If you think the error indicators are annoying, you can turn them off. Just go to File > Options > Formulas and turn off all the error checking rules you don’t need.

PS: Thanks to Justin for emailing me the Paste Special Multiply with 1 trick.