Excel 2007 : Weird result from concatenation

**magnanr** · 07-18-2012, 12:06 PM

I usually have no problems using concatenation but I am having a formatting problem for which I have not been able to find a solution so far. Perhaps some of you have seen this before and have a suggestion for me. So here goes:

I have a text value in cell A1: "Incomplete process"
I have a numerical value in cell B1: 52.9 (this is derived from another sheet in the workbook)
I am using this simple formula: =CONCATENATE(a1," ","at"," ",B1," ","per cent")
I am getting the following result (actually it's what I want): Incomplete process at 52.8571428571429 per cent
I like precision just as much as anyone else, but this is a bit overboard...

How can I simply get 52.9 instead of that elongated number? This is in Excel 2007.

Any suggestions?

Thanks!

**Pepe Le Mokko** · 07-18-2012, 12:08 PM

Replace A1 with ROUND(A1,2) perhaps

**vandan_tanna** · 07-18-2012, 12:09 PM

MAY BE

=CONCATENATE(a1," at ",ROUND(B1,2) ," per cent")

**Cutter** · 07-18-2012, 12:09 PM

You can use the TEXT() function for the B1 part.

Like TEXT(B1,"0.0")

**magnanr** · 07-18-2012, 12:10 PM

I tried it before posting, tried it again as you suggested, I guess I was making a mistake in my brackets, because it wasn't working, and now it does! Problem solved ,thank you so much.

**K m** · 07-18-2012, 12:14 PM

Try the FIXED Function ie

=CONCATENATE(a1," ","at"," ",FIXED(B1,1)," ","per cent")

or
=A1&" ","&at&"," "&fixed(B1,1)&" per cent")
One side benefit of the fixed function is that it adds commas to the result if the value is above 999 ie 1,000

Kirk