Convert text to numbers
 

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???

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 (editpaste specialvalues).

HTH

"gennario" wrote:

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???

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"gennario" wrote in message
...
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???

gennario


Copy an empty cell formatted to General or Number.

Select the column of numbers and Paste SpecialAddOKEsc.


Gord Dibben Excel MVP

On Mon, 10 Jan 2005 14:29:02 -0800, "gennario"
wrote:

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???

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!

"Jimbola" wrote:

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 (editpaste specialvalues).

HTH

"gennario" wrote:

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???

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" wrote:

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"gennario" wrote in message
...
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???

You're welcome !
Thanks for the feedback
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"gennario" wrote in message
...
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