Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Strange Format changes
Good day. I am using Excel 2002 with Windows ME. I have
a worksheet that contains product costs supplied to me by my vendor in the following format; $1.00 (US) $12.00 (US) $123.00 (US) $1234.00 (US) I have inserted two columns, one to contain a fomula and the other to copy the formula results into. The first column contains the formula shown below to extract the " (US)" from each of the costs. =LEFT(C2,SEARCH(" ",C2,1)) This appears to work although it does leave a single space character behind the actual product cost. The worksheet contains almost 300 different products and the cost for each one. After executing the formula shown above, I then copy the entire column into the second column using Paste Special and selecting Values. This appears to work also and leaves me with the product cost with a left-justified value with a dollar sign and a single space behind the cost. Eventually I would like to get rid of the space. Both of the new columns are formatted as Currency with 2 decimal places. All appears to be OK. However, if I double-click any single value in the second column and then click any other cell, the value in the double-clicked cell loses its currency formatting and changes to number format. This loses the dollar sign and the value changes from left- justified to right-justified. Also, if the value contains a zero or a double zero as part of the cents value, that zero is no longer shown even though the number format calls for 2 decimal places. I am at a loss on this one. If anyone has any suggestions, please forward them. I could also some advice on how to get rid of the space between the cost and the "(US)". Thanks, Danno... |
#2
|
|||
|
|||
Try this to strip the (US):
=INT(LEFT(A1,LEN(A1)-5)) Ernst. "Dan Wilson" wrote: Good day. I am using Excel 2002 with Windows ME. I have a worksheet that contains product costs supplied to me by my vendor in the following format; $1.00 (US) $12.00 (US) $123.00 (US) $1234.00 (US) I have inserted two columns, one to contain a fomula and the other to copy the formula results into. The first column contains the formula shown below to extract the " (US)" from each of the costs. =LEFT(C2,SEARCH(" ",C2,1)) This appears to work although it does leave a single space character behind the actual product cost. The worksheet contains almost 300 different products and the cost for each one. After executing the formula shown above, I then copy the entire column into the second column using Paste Special and selecting Values. This appears to work also and leaves me with the product cost with a left-justified value with a dollar sign and a single space behind the cost. Eventually I would like to get rid of the space. Both of the new columns are formatted as Currency with 2 decimal places. All appears to be OK. However, if I double-click any single value in the second column and then click any other cell, the value in the double-clicked cell loses its currency formatting and changes to number format. This loses the dollar sign and the value changes from left- justified to right-justified. Also, if the value contains a zero or a double zero as part of the cents value, that zero is no longer shown even though the number format calls for 2 decimal places. I am at a loss on this one. If anyone has any suggestions, please forward them. I could also some advice on how to get rid of the space between the cost and the "(US)". Thanks, Danno... |
#3
|
|||
|
|||
Hi!
=INT(LEFT(A1,LEN(A1)-5)) What if the entry is $1.50 (US) ? More robust: =--SUBSTITUTE(A1," (US)","") Format as CURRENCY. Biff -----Original Message----- Try this to strip the (US): =INT(LEFT(A1,LEN(A1)-5)) Ernst. "Dan Wilson" wrote: Good day. I am using Excel 2002 with Windows ME. I have a worksheet that contains product costs supplied to me by my vendor in the following format; $1.00 (US) $12.00 (US) $123.00 (US) $1234.00 (US) I have inserted two columns, one to contain a fomula and the other to copy the formula results into. The first column contains the formula shown below to extract the " (US)" from each of the costs. =LEFT(C2,SEARCH(" ",C2,1)) This appears to work although it does leave a single space character behind the actual product cost. The worksheet contains almost 300 different products and the cost for each one. After executing the formula shown above, I then copy the entire column into the second column using Paste Special and selecting Values. This appears to work also and leaves me with the product cost with a left-justified value with a dollar sign and a single space behind the cost. Eventually I would like to get rid of the space. Both of the new columns are formatted as Currency with 2 decimal places. All appears to be OK. However, if I double-click any single value in the second column and then click any other cell, the value in the double-clicked cell loses its currency formatting and changes to number format. This loses the dollar sign and the value changes from left- justified to right-justified. Also, if the value contains a zero or a double zero as part of the cents value, that zero is no longer shown even though the number format calls for 2 decimal places. I am at a loss on this one. If anyone has any suggestions, please forward them. I could also some advice on how to get rid of the space between the cost and the "(US)". Thanks, Danno... . |
#4
|
|||
|
|||
First of all, your formula that's doing the extracting is a text formula,
that is returning Text, *not* true numbers. Just look in the formula bar. If you see dollar signs there, you know it's not a true number. Since you already have a "helper" column formatted to Currency with 2 decimal places, there's no need for the column that contains the extraction formula to be formatted, since it's only a mid-point on the road to the final, properly formatted, final column. Just revise your formula to this: =--LEFT(C2,SEARCH(" ",C2,1)) And don't worry about trailing spaces, they'll disappear automatically, together with the dollar signs. That double unary will return *true* numbers, which, when you copy the "values" over to the Currency pre-formatted "helper" column, will give you *true* numbers, displayed in the format that you chose. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Dan Wilson" wrote in message ... Good day. I am using Excel 2002 with Windows ME. I have a worksheet that contains product costs supplied to me by my vendor in the following format; $1.00 (US) $12.00 (US) $123.00 (US) $1234.00 (US) I have inserted two columns, one to contain a fomula and the other to copy the formula results into. The first column contains the formula shown below to extract the " (US)" from each of the costs. =LEFT(C2,SEARCH(" ",C2,1)) This appears to work although it does leave a single space character behind the actual product cost. The worksheet contains almost 300 different products and the cost for each one. After executing the formula shown above, I then copy the entire column into the second column using Paste Special and selecting Values. This appears to work also and leaves me with the product cost with a left-justified value with a dollar sign and a single space behind the cost. Eventually I would like to get rid of the space. Both of the new columns are formatted as Currency with 2 decimal places. All appears to be OK. However, if I double-click any single value in the second column and then click any other cell, the value in the double-clicked cell loses its currency formatting and changes to number format. This loses the dollar sign and the value changes from left- justified to right-justified. Also, if the value contains a zero or a double zero as part of the cents value, that zero is no longer shown even though the number format calls for 2 decimal places. I am at a loss on this one. If anyone has any suggestions, please forward them. I could also some advice on how to get rid of the space between the cost and the "(US)". Thanks, Danno... |
#5
|
|||
|
|||
Good day Ragdyer. Thank you very much for taking the time
to understand my issue and provide the solution that WORKS. I try very hard to word my requests so that all the necessary data is provided. I am interested in knowing more about the "double unary" (--) just before the Left function in the formula that you sent to me. I have never encountered this before and could not find anything in the Excel Help file about this element. Thanks again for being the answer to my problem. Danno... -----Original Message----- First of all, your formula that's doing the extracting is a text formula, that is returning Text, *not* true numbers. Just look in the formula bar. If you see dollar signs there, you know it's not a true number. Since you already have a "helper" column formatted to Currency with 2 decimal places, there's no need for the column that contains the extraction formula to be formatted, since it's only a mid-point on the road to the final, properly formatted, final column. Just revise your formula to this: =--LEFT(C2,SEARCH(" ",C2,1)) And don't worry about trailing spaces, they'll disappear automatically, together with the dollar signs. That double unary will return *true* numbers, which, when you copy the "values" over to the Currency pre-formatted "helper" column, will give you *true* numbers, displayed in the format that you chose. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Dan Wilson" wrote in message ... Good day. I am using Excel 2002 with Windows ME. I have a worksheet that contains product costs supplied to me by my vendor in the following format; $1.00 (US) $12.00 (US) $123.00 (US) $1234.00 (US) I have inserted two columns, one to contain a fomula and the other to copy the formula results into. The first column contains the formula shown below to extract the " (US)" from each of the costs. =LEFT(C2,SEARCH(" ",C2,1)) This appears to work although it does leave a single space character behind the actual product cost. The worksheet contains almost 300 different products and the cost for each one. After executing the formula shown above, I then copy the entire column into the second column using Paste Special and selecting Values. This appears to work also and leaves me with the product cost with a left-justified value with a dollar sign and a single space behind the cost. Eventually I would like to get rid of the space. Both of the new columns are formatted as Currency with 2 decimal places. All appears to be OK. However, if I double-click any single value in the second column and then click any other cell, the value in the double-clicked cell loses its currency formatting and changes to number format. This loses the dollar sign and the value changes from left- justified to right-justified. Also, if the value contains a zero or a double zero as part of the cents value, that zero is no longer shown even though the number format calls for 2 decimal places. I am at a loss on this one. If anyone has any suggestions, please forward them. I could also some advice on how to get rid of the space between the cost and the "(US)". Thanks, Danno... . |
#6
|
|||
|
|||
This may shed some light on the subject:
http://www.mcgimpsey.com/excel/formulae/doubleneg.html -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit! ------------------------------------------------------------------- "Dan Wilson" wrote in message ... Good day Ragdyer. Thank you very much for taking the time to understand my issue and provide the solution that WORKS. I try very hard to word my requests so that all the necessary data is provided. I am interested in knowing more about the "double unary" (--) just before the Left function in the formula that you sent to me. I have never encountered this before and could not find anything in the Excel Help file about this element. Thanks again for being the answer to my problem. Danno... -----Original Message----- First of all, your formula that's doing the extracting is a text formula, that is returning Text, *not* true numbers. Just look in the formula bar. If you see dollar signs there, you know it's not a true number. Since you already have a "helper" column formatted to Currency with 2 decimal places, there's no need for the column that contains the extraction formula to be formatted, since it's only a mid-point on the road to the final, properly formatted, final column. Just revise your formula to this: =--LEFT(C2,SEARCH(" ",C2,1)) And don't worry about trailing spaces, they'll disappear automatically, together with the dollar signs. That double unary will return *true* numbers, which, when you copy the "values" over to the Currency pre-formatted "helper" column, will give you *true* numbers, displayed in the format that you chose. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Dan Wilson" wrote in message ... Good day. I am using Excel 2002 with Windows ME. I have a worksheet that contains product costs supplied to me by my vendor in the following format; $1.00 (US) $12.00 (US) $123.00 (US) $1234.00 (US) I have inserted two columns, one to contain a fomula and the other to copy the formula results into. The first column contains the formula shown below to extract the " (US)" from each of the costs. =LEFT(C2,SEARCH(" ",C2,1)) This appears to work although it does leave a single space character behind the actual product cost. The worksheet contains almost 300 different products and the cost for each one. After executing the formula shown above, I then copy the entire column into the second column using Paste Special and selecting Values. This appears to work also and leaves me with the product cost with a left-justified value with a dollar sign and a single space behind the cost. Eventually I would like to get rid of the space. Both of the new columns are formatted as Currency with 2 decimal places. All appears to be OK. However, if I double-click any single value in the second column and then click any other cell, the value in the double-clicked cell loses its currency formatting and changes to number format. This loses the dollar sign and the value changes from left- justified to right-justified. Also, if the value contains a zero or a double zero as part of the cents value, that zero is no longer shown even though the number format calls for 2 decimal places. I am at a loss on this one. If anyone has any suggestions, please forward them. I could also some advice on how to get rid of the space between the cost and the "(US)". Thanks, Danno... . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to format a date to a different format | Excel Discussion (Misc queries) | |||
protecting format | Excel Discussion (Misc queries) | |||
Telephone number format | Excel Discussion (Misc queries) | |||
copy conditional format as ACTUAL format | Excel Discussion (Misc queries) | |||
Copying a conditional format | Excel Worksheet Functions |