Excel how to convert text to numbers?

Question

I have a excel report which contain serveral columns of text and several columns of numbers which are stored as text.

Is there any easy way to easily convert those numbers that are stored as text to numbers easily without affecting those actual text data?

Thanks

Answer 1

set data type as number for the entire column

Answer 2

Use the VALUE function if you just want that text as a number in a different cell.

Answer 3

I'm not sure I understand your dilemma, but there are at least two solutions to your immediate question:

1. Format the cells that need to be numbers as "number" as opposed to text. You can highlight the cells you want --> right click --> Format as number. You can set the number of decimal and specific formatting from there.

2. Create a new column that multiplies your text/number column times 1 and set the format to this new column as number.

Answer 4

I find that the easiest and quickest way to convert "numbers stored as text" into numeric numbers is

1. Select any blank cell
2. Copy that cell (Ctrl+C)
3. Select the range which contains the data you want to convert (it's OK if the range includes nonnumeric data as well)
4. Use "Paste Special" with operation "add" (I guess "subtract" would work too)

Besides being quick, this has the advantage of converting in-place.

Oh, "Paste Special" is in the Edit menu of "classic" versions of Excel (like Excel 2000). I don't know if it was moved for Excel 2007 and later.