Changing text cells to currency to autosum?
Posted by Chris on February 05, 2002 5:08 PM
Ok, here is my problem. My clients have sent in excel sheets where we need to sum the dollar amount to get the totals etc... Well this usually works out fine as most people have it formatted as something other then text (currency, number, etc..) that can be Auto-summed below the column.
The only way I know of to fix this problem is to highlight the column and change the format to currency and then re-key the entire column since if you dont re-key it will not accept the change in format, or at least I dont know of a way to make it accept those changes to format without doing this. As you might imagine, trying this with a 10 line template isnt bad but staring at a 4000 row template is a little un-nerving! hahaha Thanks in advance for any help!
Posted by Paul on February 05, 2002 6:31 PM
ASAP utilities will do it, it is a free add in for excel, take a look at it at http://www.asaputilities.com/ if you use it look under numbers, convert text numbers to numbers
Posted by Yogi Anand on February 05, 2002 6:32 PM
Hi Chris:
It is an interesting problem -- the numeric entries that are indeed text (recognizable with the '(apostrophe) character at extreme left, formatting them to FORMAT|CELLS|NUMBER|GENERAL etc does not work.
Here is a trick that does work. Highlite the cells that have numeric entries that are text, and multiply each of them with 1 ... I would do using EDIT|COPY then EDIT|PASTE_SPECIAL|MULTIPLY, and that would convert the entire set (thousands of rows, tens of columns of entries) into numeric entries in no time.
HTH
Yogi Anand
ANAND Enterprises (broken link)
Posted by Paul on February 05, 2002 6:37 PM
One more thing
you need to change the format of the cells before you run convert numbers
Posted by Chris on February 05, 2002 8:01 PM
Ok, I probably am doing something wrong, well actually I know I am. When I do the Edit/Copy and then do the Paste Special and select Multiply it doesnt give me an option to multiply by 1? Whats the simple step I am leaving out?? Thanks!
Posted by Chris on February 05, 2002 8:12 PM
Re: One more thing
Thanks, I am downloading that utility now however I am still interested in knowing the manual way as this is something I do at work and I am not sure just how keen they are on me adding outside add-ins onto Excel. Of course if this helps productivity without any problems I doubt they would mind! hahaha Either way I a want this on my computer at home as it has a ton of useful stuff it seems! thanks!
Posted by Yogi Anand on February 05, 2002 8:21 PM
Hi Chris:
In reference to my response regarding multiplying numeric entries formatted as text by 1, forgive me I did not detail the multiplication by 1 to spare you unnecessary detail -- this is what you do.
Suppose entries of interest are in cells A1:D4550
Then in some cell outside this range, say in cell E1, key in 1. Then highlite E1, then EDIT|COPY; then highlite A1:D4550, then EDIT|PASTE_SPECIAL|(operation)MULTIPLY and ENTER ... Bingo!
HTH
Yogi Anand
ANAND Enterprises (broken link)
: Hi Chris
Posted by Chris on February 05, 2002 8:33 PM
Thanks man, you are the bomb! hahaha This will save me a lot of time with these older templates we have. I knew there had to be an easy way to do this. Thanks again!
Chris In reference to my response regarding multiplying numeric entries formatted as text by 1, forgive me I did not detail the multiplication by 1 to spare you unnecessary detail -- this is what you do. Then in some cell outside this range, say in cell E1, key in 1. Then highlite E1, then EDIT|COPY; then highlite A1:D4550, then EDIT|PASTE_SPECIAL|(operation)MULTIPLY and ENTER ... Bingo! : Ok, I probably am doing something wrong, well actually I know I am. When I do the Edit/Copy and then do the Paste Special and select Multiply it doesnt give me an option to multiply by 1? Whats the simple step I am leaving out?? Thanks! :
