Convert text to numbers

[Guest]

I have a large spreadsheet with a column that has the numbers formatted as
text. I tried reformatting the column from numbers to text but when I sum the
column with other columns (that are formatted as numbers) it still doesn't
show in the sum. The spreadsheet is too big for me to retype the numbers. Can
anyone please help???

 

[Guest]

In a another column insert a formula that multiplies the first colum that you
are having problems with by 1. i.e = a1*1 and drag all the way down.
This will force Excel to create a result that is a number. Then copy this
new results column on top of the old one, make sure when you paste that you
paste as values (edit>paste special>values).

HTH

 

[Max]

Try this on a spare copy ..

Assume the prob lies with say, col B
where B1 contains a label,
with data in B2 down

Select the column B
Click Data > Text to Columns
Click Finish

 

[Gord Dibben]

gennario


Copy an empty cell formatted to General or Number.

Select the column of numbers and Paste Special>Add>OK>Esc.


Gord Dibben Excel MVP

 

[Guest]

THANKS! I actually had to create the new column and then format the entire
column to a number otherwise the formula was actually typed as text. Once the
new column was formatted I then added the formula and it worked great (had to
be sure copied and pasted as value). What a pain but it sure beats typing
retyping 500 lines of data!

 

[Guest]

Thanks!! Definitely the easiest way to go but the only drawback is that it
keeps the data as text so if the number changes, the formula doesn't always.
This will probably work for what I need since I shouldn't be changing
numbers. Thanks again

 

[Max]

You're welcome !
Thanks for the feedback