Add additiona1 $. 0 to Concatenate value

[johndrew]

I have the following formula in "A1":

="$"&ROUND(G3,2)& " per sq ft"

In G3, I have $7.00 or $7.10

The problem is I get $7 or if I have $7.10 is drops the zero and shows $7.1


What I want is to show a $ value to 2 decimals not drop them.

 

[Weazel]

maybe...

="$"&TEXT(ROUND(G3,2),"0.00")& " per sq ft"

 

[johndrew]

Thanks Weazel, that's just what I needed. Cheers

 

[Rick Rothstein]

maybe...

="$"&TEXT(ROUND(G3,2),"0.00")& " per sq ft"

You can avoid having Excel do one concatenation by moving the $ sign inside the TEXT function's pattern string...

=TEXT(ROUND(G3,2),"$0.00")& " per sq ft"

 

[Weazel]

Thanks Rick, I didn't think of doing it that way

 

[Rick Rothstein]

Thanks Rick, I didn't think of doing it that way

Actually, in thinking about it some more, we can eliminate both concatenations (the less work for Excel to do, the better)...

=TEXT(ROUND(G3,2),"$0.00"" per sq ft""")

 

[Weazel]

that works...though I'm a little confused on how the double double quotes are working

 

[Rick Rothstein]

that works...though I'm a little confused on how the double double quotes are working

The second argument to the TEXT function is a quoted text constant... in order to protect its characters from being interpreted as meta-characters to be acted on by the TEXT function, they need to be quoted... when you embed quoted text with the quote marks delineating a text constant, you must double up the quote marks surrounding the quoted text being embedded.

 

[Weazel]

Ok, I see now, I think my knee jerk reaction to the original formula would be use the ampersand's and concatenate the strings together. I'm wondering how if there is any signifcant improvement in calculation time. I'll have to play with it a little I think. Thanks for that.