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 -- anandmr65 ------------------------------------------------------------------------ anandmr65's Profile: http://www.excelforum.com/member.php...o&userid=30728 View this thread: http://www.excelforum.com/showthread...hreadid=507118 |
Numbers read as text
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 |
Numbers read as text
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 |
Numbers read as text
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 |
Numbers read as text
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 |
Numbers read as text
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 |
All times are GMT +1. The time now is 10:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com