Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Operations (add, multiply) on all values ($00.00) in a column?
Hello, I have a file with a few columns with prices that are in this format - $00.00 (with a point, not comma). Is it possible to quickly change all values in a column, for example increasing all prices in a column with 4$ or a certain percentage. I tried with Paste Special, but it's not working for some reason. I guess it's because Excel is not treating the values in the $00.00 format as numbers. So is there any way to convert the prices to numbers and then after doing Paste Special (with add/multiply) to them to convert them back to the original format? I have attached a sample file to show what I'm trying to do (in case my explanations are not making much sense :), sorry for the bad english). Hope someone can help me with this. Thank you very much in advance. +-------------------------------------------------------------------+ |Filename: Prices.zip | |Download: http://www.excelforum.com/attachment.php?postid=4556 | +-------------------------------------------------------------------+ -- DanielG ------------------------------------------------------------------------ DanielG's Profile: http://www.excelforum.com/member.php...o&userid=33056 View this thread: http://www.excelforum.com/showthread...hreadid=528715 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Operations (add, multiply) on all values ($00.00) in a column?
You might have imported these values so there might be leading and trailing
spaces html etc, insert a help column and assume the values start in A1 use =4+TRIM(SUBSTITUTE(A1,CHAR(160),"")) as you can see I did the increase as well copy down as long as needed, copy and paste special as values and format as currency, delete help column. If you do this regularly you can use http://www.mvps.org/dmcritchie/excel/join.htm#trimall a macro that will clean the cells -- Regards, Peo Sjoblom http://nwexcelsolutions.com "DanielG" wrote in message ... Hello, I have a file with a few columns with prices that are in this format - $00.00 (with a point, not comma). Is it possible to quickly change all values in a column, for example increasing all prices in a column with 4$ or a certain percentage. I tried with Paste Special, but it's not working for some reason. I guess it's because Excel is not treating the values in the $00.00 format as numbers. So is there any way to convert the prices to numbers and then after doing Paste Special (with add/multiply) to them to convert them back to the original format? I have attached a sample file to show what I'm trying to do (in case my explanations are not making much sense :), sorry for the bad english). Hope someone can help me with this. Thank you very much in advance. +-------------------------------------------------------------------+ |Filename: Prices.zip | |Download: http://www.excelforum.com/attachment.php?postid=4556 | +-------------------------------------------------------------------+ -- DanielG ------------------------------------------------------------------------ DanielG's Profile: http://www.excelforum.com/member.php...o&userid=33056 View this thread: http://www.excelforum.com/showthread...hreadid=528715 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Operations (add, multiply) on all values ($00.00) in a column?
Thank you for the answer Peo. You are right that the file is imported so the problem is that after I change the values and convert them to currency, they are no longer in their original format and cannot be imported. Is their any way to convert the currency format - $00,00 (with a comma) right-aligned - to the original format - $00.00 (with a point) left-align, which I believe is text? -- DanielG ------------------------------------------------------------------------ DanielG's Profile: http://www.excelforum.com/member.php...o&userid=33056 View this thread: http://www.excelforum.com/showthread...hreadid=528715 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Divide Column A by Column B multiply Column C | Excel Worksheet Functions | |||
match and count words | Excel Worksheet Functions | |||
How sum values in column B using values in column A as the conditi | Excel Worksheet Functions |