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...