Format and Concatenate a number

[rfinnegan]

Hi All:

I have a field in a dashboard report where I need to concatenate a word and a number. I've done it succesfully in 5 other fields using the formula structure below, but in this case, the numbers I am working with are the result of calculations elsewhere in the workbook.

The result of those calculations is sometimes a number that is NOT a whole number. For example, 3327.59516129032 is one of those number.

So using the formula below I get "Prod. Goal 3327.59516129032" I only have space for, and only want to see "Prod. Goal 3328"

I've changed the format of cell C40, but it didn't make a difference.

=CONCATENATE("Prod. Goal"," ",C40)

Any help is appreciated and as always, thanks in advance.

 

[Jonmo1]

Try

="Prod. Goal " & TEXT(C40,"0")


Hope that helps.

 

[Smitty]

Just a heads up that you can throw CONCATENATE out the window, as the ampersand (&) in Jon's example is much more useful.

As for why you got that behavior, Excel doesn't know your intentions when you add text to a cell reference, so it converts it to its lowest element. You use TEXT to convert it to the format you want.

HTH,