concatenating a 2 decimal place number

[bob]

Hello,

I have stubbled across this problem a couple of time and
would like to know how to solve it....

I have a formula that gives a long decimal value, say
1.23456789 in cell A1. I would like to have control over
the decimal value into the concatenated cell. For
instance, =concatenate(A1, " years") appears as 1.23456789
years, but I would like it to be 1.23 years. How do I
control the decimal values in a concatenated cell???

Thanks for your insight!

 

[Juan Sanchez]

Bob

Several ways:

=TEXT(A1,"0.00")& " Years"
=ROUND(A1,2) & " Years"
=ROUNDUP(A1,2)&" Years"
=ROUNDDOWN(A1,2)&" Years"

Or you can custom format : FORMAT > CELLS > NUMBER...

Custom Format: 0.00 " Years"... this way it is still a
number in case you want to use it in a function...

Cheers
Juan

 

[Mike Lewis]

The TEXT function will work:

=TEXT(I24,"#.##")&" Years "

Will give you two deciamal places.

HTH

Mike

 

[Bob]

Juan,

You must be the concatenate master... You had responded to
the previous queries I read when searching for concatenate.

Many thanks for your solutions. So I guess one can not use
concatenate for this situation. Is it something to do
with the format that the concatenate function uses? Does
it have to be text?

Regards,
-Bob

 

[Dave Peterson]

The & is quicker for most to type.

But
=TEXT(A1,"0.00")& " Years"
could be rewritten as:
=concatenate(TEXT(A1,"0.00"), " Years")

The =TEXT() function makes sure you have the formatting correct.

 

[Juan Sanchez]

=concatenate( works, but is to long to write and i'm
lazy

the apersand (&) is faster and does the same... cheers.
Juan

 

[Harlan Grove]

The & is quicker for most to type.

....

And it won't eat a nested function call level. There's *never* a good reason
to use the CONCATENATE function, just as there's never a good reason to use
SUM to sum two numbers rather than using +.

 

[BrianB]

=CONCATENATE(ROUND(A1,2), " years"