Hi,

I have a column that should have numbers. I copy the data from some
html source to excel. When the data is copied the numbers are treated
as text and the numerical functions such as SUM does not work & even in
pivot tables the sum etc. are not working. I tried to convert these to
numbers using value(0 function, but gives a formula error (#name etc.)
cold somebody suggest a way to tackle this problem. I tried copying the
complete column to another sheet & back but it still remails as a text.

Please help

Thanks
Anand


--
anandmr65
------------------------------------------------------------------------
anandmr65's Profile: http://www.excelforum.com/member.php...o&userid=30728
View this thread: http://www.excelforum.com/showthread...hreadid=507118

Hi Anand,
What happens if you commit your formula as an array formula with Ctrl +
Shift + Enter.
I tried =SUM(VALUE(A1:A5)) when A1:A5 contained text numbers and it
only worked when commited as an array formula.

Hope this helps

Ken Johnson

Ken Johnson Wrote:
Hi Anand,
What happens if you commit your formula as an array formula with Ctrl
+
Shift + Enter.
I tried =SUM(VALUE(A1:A5)) when A1:A5 contained text numbers and it
only worked when commited as an array formula.

Hope this helps

Ken Johnson


Thanks Ken Johnson,

Since these numbers are used by Pivot table also, the array formula
solution will not work there, may be there must be a method where we
can convert the text to numbers by Paste special - add option.

Thanks again for the concern.

Regards
Anand


--
anandmr65
------------------------------------------------------------------------
anandmr65's Profile: http://www.excelforum.com/member.php...o&userid=30728
View this thread: http://www.excelforum.com/showthread...hreadid=507118

Hi Anand,
I don't know which column contains the numbers as text. For argument's
sake say the affected column is column A, then in row 1 of any spare
empty column just type the formula =VALUE(A1), then fill down to be
level with the last nonblank cell in the affected column. Select this
column of VALUEs then copy then select the top cell of column A and
Paste Special Values. Then delete the column of VALUEs.

That should convert the numbers as text to normal numbers.

Ken Johnson

I think it's usually best to clean up the raw data.

You may want to try David McRitchie's routine to clean the data:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

anandmr65 wrote:

Hi,

I have a column that should have numbers. I copy the data from some
html source to excel. When the data is copied the numbers are treated
as text and the numerical functions such as SUM does not work & even in
pivot tables the sum etc. are not working. I tried to convert these to
numbers using value(0 function, but gives a formula error (#name etc.)
cold somebody suggest a way to tackle this problem. I tried copying the
complete column to another sheet & back but it still remails as a text.

Please help

Thanks
Anand

--
anandmr65
------------------------------------------------------------------------
anandmr65's Profile: http://www.excelforum.com/member.php...o&userid=30728
View this thread: http://www.excelforum.com/showthread...hreadid=507118

--

Dave Peterson

hi,

I've just had the same problem, are you using 2003 if so allow the eror
check to work you will have the little green triangle in top left of the
cell. The error indicator will allow you to change from Text to number,
there is an option there.

to activate error check, tools, options, error checking and tick box in
settings.

hope this helps


--
Clash
------------------------------------------------------------------------
Clash's Profile: http://www.excelforum.com/member.php...o&userid=18951
View this thread: http://www.excelforum.com/showthread...hreadid=507118