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

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

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

.

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

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


.

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


.