Les Jones

Tech > Microsoft Excel - Forcing Numbers to be Treated as Numbers instead of Text

I ran into a problem with a spreadsheet I use heavily at work. Actually, I had a bunch of weird problems none of which made any sense. Eventually I realized that Excel was formatting some of the number cells as Text or General rather than Number. That causes all sorts of odd problems in functions that expect to see numbers and won't recognize them if they're in a cell that isn't formatted for Number.

I tried the super-obvious solution of selecting the cells and setting Format to Number. Didn't work even after a zillion repetitions. I eventually Googled until I found this solution.

1. Enter the value 1 in an empty cell. 2. Select the cell and press Ctrl+C. The value is now in the Clipboard. 3. Select the range of cells you want to convert to numbers. 4. Choose Paste Special from the Edit menu. Excel displays the Paste Special dialog box. 5. Make sure the Multiply radio button is selected. 6. Click on OK.

This works because Excel multiples each cell in the range (step 3) by the value in the Clipboard and then again stores the value in the cell. Since any number multiplied by one is that same number, you effectively force Excel to replace the contents of the cell with the numerical equivalent of the text that was previously there.