Decimal Place goes crazy when using the "&" Sign.
 

Hi,
Basic Formula in A1 says: B1*C1. This works.
I have all cells formatted to Number Format with 0 decimal place(s). I also
have the 1000 separator (,) check box checked.

If I change the same formula in A1 to: B1*C1&" lbs." .....A1 gives the
right answer but shows a zillion decimal places with it.

Is there a tip anyone could give me on what is happening and a suggestion
for getting rid of the decimal places?

Because of the &" lbs." does Excel maybe think the formula is text? I don't
know.

Thank-you in advance,
Amy

Decimal Place goes crazy when using the "&" Sign.
 

When you use A1 in a formula, it uses the contents of the cell, and ignores
formats. Formats control only the display of that cell, and have no impact
on other formulas. To include formatting in your formula, use the Text
function, as in:
=Text(B1*C1,"#,##0)&" lbs."

Or, use the formula:
=b1*c1
and set a custom format of: #,##0" lbs"

Regards,
Fred

"spreadsheetlady" wrote in
message ...
Hi,
Basic Formula in A1 says: B1*C1. This works.
I have all cells formatted to Number Format with 0 decimal place(s). I
also
have the 1000 separator (,) check box checked.

If I change the same formula in A1 to: B1*C1&" lbs." .....A1 gives
the
right answer but shows a zillion decimal places with it.

Is there a tip anyone could give me on what is happening and a suggestion
for getting rid of the decimal places?

Because of the &" lbs." does Excel maybe think the formula is text? I
don't
know.

Thank-you in advance,
Amy

Decimal Place goes crazy when using the "&" Sign.
 

=TEXT(B1*C1,"#,##0")&" lbs"


"spreadsheetlady" wrote:

Hi,
Basic Formula in A1 says: B1*C1. This works.
I have all cells formatted to Number Format with 0 decimal place(s). I also
have the 1000 separator (,) check box checked.

If I change the same formula in A1 to: B1*C1&" lbs." .....A1 gives the
right answer but shows a zillion decimal places with it.

Is there a tip anyone could give me on what is happening and a suggestion
for getting rid of the decimal places?

Because of the &" lbs." does Excel maybe think the formula is text? I don't
know.

Thank-you in advance,
Amy

Decimal Place goes crazy when using the "&" Sign.
 

When you concatenate the lb to the end of the number you change the value to
text. Once it is text your formats don't apply. There are 2 choices. The
first is to use teh Text Formula as Teehtless mama shows (have not seen you
in a while
Teethless... good to have ya back)

The other option is to use a custom format
Format Cells... - Number - Custom
#,### "lb"

The benefit to the custom format is your number is still a number and it can
be used in further calculations.
--
HTH...

Jim Thomlinson


"spreadsheetlady" wrote:

Hi,
Basic Formula in A1 says: B1*C1. This works.
I have all cells formatted to Number Format with 0 decimal place(s). I also
have the 1000 separator (,) check box checked.

If I change the same formula in A1 to: B1*C1&" lbs." .....A1 gives the
right answer but shows a zillion decimal places with it.

Is there a tip anyone could give me on what is happening and a suggestion
for getting rid of the decimal places?

Because of the &" lbs." does Excel maybe think the formula is text? I don't
know.

Thank-you in advance,
Amy

Decimal Place goes crazy when using the "&" Sign.
 

Thank-you Teethless, Jim & Fred.
The TEXT Function fixed it.
I'm also studying the Custom Formatting. I've always noticed this feature
but never persued it. Now is the time.
Appreciate it.
Amy


"Fred Smith" wrote:

When you use A1 in a formula, it uses the contents of the cell, and ignores
formats. Formats control only the display of that cell, and have no impact
on other formulas. To include formatting in your formula, use the Text
function, as in:
=Text(B1*C1,"#,##0)&" lbs."

Or, use the formula:
=b1*c1
and set a custom format of: #,##0" lbs"

Regards,
Fred

"spreadsheetlady" wrote in
message ...
Hi,
Basic Formula in A1 says: B1*C1. This works.
I have all cells formatted to Number Format with 0 decimal place(s). I
also
have the 1000 separator (,) check box checked.

If I change the same formula in A1 to: B1*C1&" lbs." .....A1 gives
the
right answer but shows a zillion decimal places with it.

Is there a tip anyone could give me on what is happening and a suggestion
for getting rid of the decimal places?

Because of the &" lbs." does Excel maybe think the formula is text? I
don't
know.

Thank-you in advance,
Amy


.