Just on a hunch, do your "numbers" appear to have blank spaces either before or after them? If so, those blank spaces may be ASCII 160 characters--which will definitely cause a #VALUE! error when you try to do math.
To get rid of them:
=SUBSTITUTE(A1, CHAR(160),"")
You can then do the trick with copying a blank and then Edit...Paste Special...Add to convert a block of cells from text to numbers en masse.
Or you can use the Edit...Replace menu item. To create the ASCII 160 character for the Find field, hold the ALT key down and enter 0160 using the numeric keypad. This trick won't work with the numbers above QWERTY!
Main Topics
Browse All Topics
by: zorvekPosted on 2007-03-29 at 21:02:25ID: 18821504
Sometimes numbers, dates, and times can be entered into worksheet as text values which prevents them from being formatted and sorted. This frequently happens when data is imported from an external source such as a database. To convert such values to real numbers, dates, and/or times, select any unused empty cell and choose the Edit->Copy menu command. Select the cells containing the text version of the numbers or dates to be converted. Choose the menu command Edit->Paste Special. Select the Values radio button in the Paste section, the Add radio button in the Operation section, and click OK. If any dates show as numbers then select those cells and choose the menu command Format->Cells, navigate to the Number tab, select Date in left list and the desired date format from the right list. Click OK.
When importing data from external sources the values can contain non-breaking space characters (character code 160) which is hard to remove and causes numeric values and dates to be interpreted as text.
To find and replace instances of the non-breaking space character, select the cells to be cleaned and press CTRL+H to open the Replace dialog. Click in the "Find what" text entry field and clear any text (press CTRL+LEFT, press CTRL+SHIFT+RIGHT, and press DELETE). While holding the ALT key down enter 0160 on the numeric keypad. Click in the "Replace with" text entry field and clear any text (press CTRL+LEFT, press CTRL+SHIFT+RIGHT, and press DELETE). Click the "Replace All" command button.
Note: If using a laptop put the keyboard into number lock mode (by pressing the "Num Lk" key) and enter the numbers using the numeric keypad by pressing FN+ALT+digit where digit is any of the keys M=0, J=1, K=2, L=3, U=4, I=5, O=6, 7=7, 8=8, or 9=9.
Kevin