Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home



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!


Re: Changing text cells to currency to autosum?

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


Re: Changing text cells to currency to autosum?

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)


One more thing

Posted by Paul on February 05, 2002 6:37 PM
you need to change the format of the cells before you run convert numbers


Re: Changing text cells to currency to autosum?

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!



Re: One more thing

Posted by Chris on February 05, 2002 8:12 PM
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!


Re: Changing text cells to currency to autosum?

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



Re: Changing text cells to currency to autosum?

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! :





This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.