Formatting concatenated numbers

[bob]

hello excel users...

over the years I have used the CONCATENATE function and
have never been able to format the number that is being
concatenated. Example:
A
1 5.3
2 2.3
3 4.7
4 12.354 kW

In this example, I have summed the numbers in
column 'A1:A3' which have been formatted to show only 1
decimal place. This total, shown in cell A4, is obtained
using the following formula: =CONCATENATE(SUM(A1:A3),"
kW"). With this formula, the cell does not react to
formatting the decimal places in the number.

My only thoughts is that using CONCATENATE cause the
number to be converted to text, which cannot be formatted
as a number...not sure on this one. If you have any
insight to this please let me know!

Many thanks!
-Bob

 

[Aladin Akyurek]

=TEXT(SUM(A1:A3),"0.0")&" kW"

 

[Dave R.]

This may work for you;

=+ROUND(SUM(A1:A3),1)&"kW"

 

[Alan]

Try using just =SUM(A1:A) and Custom Format as
0.00" KW"
Adjust 0.00 to how many decimal places are required.

 

[Guest]

They have all worked wonderfully, thanks for your help.
To narrow down my assumption though, does concatenate make
things text? Why is it not possible to format the
concatenated cell?
-Bob

 

[Dave R.]

A quick experiment (Concatenate(1.12123,2) results in 1.121232 - which is a
number that Excel can do math with.

Trying to format this cell to 1 decimal place has no effect though.

 

[Steve]

You are right in that concatenate makes the data into
text.

If you want to show the result as a number and have
the kW after it then just format it that way - just add
kW after the custom format you want.

Steve.


Aladin Akyurek wrote