Excel: Convert cells from Text to Numeric

Something odd happened today – I was working on an Excel worksheet and a column wasn’t adding up correctly.  It’s really freaky when you have thirty plus rows of numbers and they aren’t adding up to what you think they will – then you pull out a calculator and realize that Excel wasn’t adding them up right.

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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s