Numbers read as text

  • Thread starter Thread starter anandmr65
  • Start date Start date
A

anandmr65

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
 
Ken said:
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
Click to expand...


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
 
hi,

I've just had the same problem, are you using 2003 if so allow the ero
check to work you will have the little green triangle in top left of th
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 i
settings.

hope this help
 
Back
Top