Excel: Convert cells from Text to Numeric
Current Mood:
Why? Because some of the cells in the column were defined as text and simply changing the format from text to numeric won't automatically convert them. (As a side note, changing the format DID point out which ones were incorrectly formatted).
So how do you fix this? One way is to retype them. That's a lot of fun and I suggest everyone do that at least once just so you will realize that it would have been infinitely more productive if you had converted the column to numeric before pasting in the set of numbers in the first place.
The alternative is to use the copy/paste special method:
First, enter the value 1 in an empty cell (that has been formatted as numeric), then select the cell, right click and choose COPY. Next, highlight the range of cells that you want to convert, right click and choose PASTE SPECIAL. Excel will prompt you for what you want to copy - choose the "Multiply" radio button and click okay. Excel will multiply each of the values in your selected area by "1", effectively converting them to their real numeric value.
That's better, now go get another cup of coffee.
Comments