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