Numbers read as text

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

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

Originally Posted by Ken Johnson

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

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