Strange Formating problem with text and formula in same cell

Hello, I am trying to do something for work where I need text and a formula in the same cell, and I have a formula that works, see below:

"at the rate of $"&('[RATE.xls]Rate1'!$L$954)&" per Hour"

But the problem is if the value the formula is pulling in is something like $34.10 per hour, (where the second decimal digit is a zero) the result shows:

at the rate of $34.1 per Hour

I don’t want it to show $34.1 as the value, I need it to show $34.10, I need that zero on the end.

I have spent an hour trying to make it work to no avail, does anyone have any suggestions? Is there a FORMAT function I can stick on there?

Thanks a bunch!!!

try

"at the rate of $"&fixed(('[RATE.xls]Rate1'!$L$954),2)&" per Hour"


R

awesome!! thanks

This works:

"at the rate of "&DOLLAR('[RATE.xls]Rate1'!$L$954)&" per Hour"

Regards,
Ron

On Wed, 3 Aug 2005 18:35:57 -0500, caliskier
<caliskier.1t7qme_1123113935.0338@excelforum-nospam.com> wrote:

>"at the rate of $"&('[RATE.xls]Rate1'!$L$954)&" per Hour"

You could use the TEXT worksheet function.

Something like:

"at the rate of "&TEXT([RATE.xls]Rate1!$L$954),"$#,##0.00")&" per Hour"

or, if you wanted the result to be a number that you could use in other cells,
you could use the formula: ='[RATE.xls]Rate1'!$L$954

and the custom format of:

"at the rate of "$#,##0.00" per hour"


Format/Cells/Number/Custom Type: "at the rate of "$#,##0.00" per hour"





--ron

On Wed, 3 Aug 2005 18:35:57 -0500, caliskier
<caliskier.1t7qme_1123113935.0338@excelforum-nospam.com> wrote:

>"at the rate of $"&('[RATE.xls]Rate1'!$L$954)&" per Hour"

You could use the TEXT worksheet function.

Something like:

"at the rate of "&TEXT([RATE.xls]Rate1!$L$954),"$#,##0.00")&" per Hour"

or, if you wanted the result to be a number that you could use in other cells,
you could use the formula: ='[RATE.xls]Rate1'!$L$954

and the custom format of:

"at the rate of "$#,##0.00" per hour"


Format/Cells/Number/Custom Type: "at the rate of "$#,##0.00" per hour"





--ron

On Wed, 3 Aug 2005 18:35:57 -0500, caliskier
<caliskier.1t7qme_1123113935.0338@excelforum-nospam.com> wrote:

>"at the rate of $"&('[RATE.xls]Rate1'!$L$954)&" per Hour"

You could use the TEXT worksheet function.

Something like:

"at the rate of "&TEXT([RATE.xls]Rate1!$L$954),"$#,##0.00")&" per Hour"

or, if you wanted the result to be a number that you could use in other cells,
you could use the formula: ='[RATE.xls]Rate1'!$L$954

and the custom format of:

"at the rate of "$#,##0.00" per hour"


Format/Cells/Number/Custom Type: "at the rate of "$#,##0.00" per hour"





--ron

On Wed, 3 Aug 2005 18:35:57 -0500, caliskier
<caliskier.1t7qme_1123113935.0338@excelforum-nospam.com> wrote:

>"at the rate of $"&('[RATE.xls]Rate1'!$L$954)&" per Hour"

You could use the TEXT worksheet function.

Something like:

"at the rate of "&TEXT([RATE.xls]Rate1!$L$954),"$#,##0.00")&" per Hour"

or, if you wanted the result to be a number that you could use in other cells,
you could use the formula: ='[RATE.xls]Rate1'!$L$954

and the custom format of:

"at the rate of "$#,##0.00" per hour"


Format/Cells/Number/Custom Type: "at the rate of "$#,##0.00" per hour"





--ron

On Wed, 3 Aug 2005 18:35:57 -0500, caliskier
<caliskier.1t7qme_1123113935.0338@excelforum-nospam.com> wrote:

>"at the rate of $"&('[RATE.xls]Rate1'!$L$954)&" per Hour"

You could use the TEXT worksheet function.

Something like:

"at the rate of "&TEXT([RATE.xls]Rate1!$L$954),"$#,##0.00")&" per Hour"

or, if you wanted the result to be a number that you could use in other cells,
you could use the formula: ='[RATE.xls]Rate1'!$L$954

and the custom format of:

"at the rate of "$#,##0.00" per hour"


Format/Cells/Number/Custom Type: "at the rate of "$#,##0.00" per hour"





--ron

On Wed, 3 Aug 2005 18:35:57 -0500, caliskier
<caliskier.1t7qme_1123113935.0338@excelforum-nospam.com> wrote:

>"at the rate of $"&('[RATE.xls]Rate1'!$L$954)&" per Hour"

You could use the TEXT worksheet function.

Something like:

"at the rate of "&TEXT([RATE.xls]Rate1!$L$954),"$#,##0.00")&" per Hour"

or, if you wanted the result to be a number that you could use in other cells,
you could use the formula: ='[RATE.xls]Rate1'!$L$954

and the custom format of:

"at the rate of "$#,##0.00" per hour"


Format/Cells/Number/Custom Type: "at the rate of "$#,##0.00" per hour"





--ron

On Wed, 3 Aug 2005 18:35:57 -0500, caliskier
<caliskier.1t7qme_1123113935.0338@excelforum-nospam.com> wrote:

>"at the rate of $"&('[RATE.xls]Rate1'!$L$954)&" per Hour"

You could use the TEXT worksheet function.

Something like:

"at the rate of "&TEXT([RATE.xls]Rate1!$L$954),"$#,##0.00")&" per Hour"

or, if you wanted the result to be a number that you could use in other cells,
you could use the formula: ='[RATE.xls]Rate1'!$L$954

and the custom format of:

"at the rate of "$#,##0.00" per hour"


Format/Cells/Number/Custom Type: "at the rate of "$#,##0.00" per hour"





--ron

On Wed, 3 Aug 2005 18:35:57 -0500, caliskier
<caliskier.1t7qme_1123113935.0338@excelforum-nospam.com> wrote:

>"at the rate of $"&('[RATE.xls]Rate1'!$L$954)&" per Hour"

You could use the TEXT worksheet function.

Something like:

"at the rate of "&TEXT([RATE.xls]Rate1!$L$954),"$#,##0.00")&" per Hour"

or, if you wanted the result to be a number that you could use in other cells,
you could use the formula: ='[RATE.xls]Rate1'!$L$954

and the custom format of:

"at the rate of "$#,##0.00" per hour"


Format/Cells/Number/Custom Type: "at the rate of "$#,##0.00" per hour"





--ron

On Wed, 3 Aug 2005 18:35:57 -0500, caliskier
<caliskier.1t7qme_1123113935.0338@excelforum-nospam.com> wrote:

>"at the rate of $"&('[RATE.xls]Rate1'!$L$954)&" per Hour"

You could use the TEXT worksheet function.

Something like:

"at the rate of "&TEXT([RATE.xls]Rate1!$L$954),"$#,##0.00")&" per Hour"

or, if you wanted the result to be a number that you could use in other cells,
you could use the formula: ='[RATE.xls]Rate1'!$L$954

and the custom format of:

"at the rate of "$#,##0.00" per hour"


Format/Cells/Number/Custom Type: "at the rate of "$#,##0.00" per hour"





--ron

On Wed, 3 Aug 2005 18:35:57 -0500, caliskier
<caliskier.1t7qme_1123113935.0338@excelforum-nospam.com> wrote:

>"at the rate of $"&('[RATE.xls]Rate1'!$L$954)&" per Hour"

You could use the TEXT worksheet function.

Something like:

"at the rate of "&TEXT([RATE.xls]Rate1!$L$954),"$#,##0.00")&" per Hour"

or, if you wanted the result to be a number that you could use in other cells,
you could use the formula: ='[RATE.xls]Rate1'!$L$954

and the custom format of:

"at the rate of "$#,##0.00" per hour"


Format/Cells/Number/Custom Type: "at the rate of "$#,##0.00" per hour"





--ron

On Wed, 3 Aug 2005 18:35:57 -0500, caliskier
<caliskier.1t7qme_1123113935.0338@excelforum-nospam.com> wrote:

>"at the rate of $"&('[RATE.xls]Rate1'!$L$954)&" per Hour"

You could use the TEXT worksheet function.

Something like:

"at the rate of "&TEXT([RATE.xls]Rate1!$L$954),"$#,##0.00")&" per Hour"

or, if you wanted the result to be a number that you could use in other cells,
you could use the formula: ='[RATE.xls]Rate1'!$L$954

and the custom format of:

"at the rate of "$#,##0.00" per hour"


Format/Cells/Number/Custom Type: "at the rate of "$#,##0.00" per hour"





--ron

On Wed, 3 Aug 2005 18:35:57 -0500, caliskier
<caliskier.1t7qme_1123113935.0338@excelforum-nospam.com> wrote:

>"at the rate of $"&('[RATE.xls]Rate1'!$L$954)&" per Hour"

You could use the TEXT worksheet function.

Something like:

"at the rate of "&TEXT([RATE.xls]Rate1!$L$954),"$#,##0.00")&" per Hour"

or, if you wanted the result to be a number that you could use in other cells,
you could use the formula: ='[RATE.xls]Rate1'!$L$954

and the custom format of:

"at the rate of "$#,##0.00" per hour"


Format/Cells/Number/Custom Type: "at the rate of "$#,##0.00" per hour"





--ron

On Wed, 3 Aug 2005 18:35:57 -0500, caliskier
<caliskier.1t7qme_1123113935.0338@excelforum-nospam.com> wrote:

>"at the rate of $"&('[RATE.xls]Rate1'!$L$954)&" per Hour"

You could use the TEXT worksheet function.

Something like:

"at the rate of "&TEXT([RATE.xls]Rate1!$L$954),"$#,##0.00")&" per Hour"

or, if you wanted the result to be a number that you could use in other cells,
you could use the formula: ='[RATE.xls]Rate1'!$L$954

and the custom format of:

"at the rate of "$#,##0.00" per hour"


Format/Cells/Number/Custom Type: "at the rate of "$#,##0.00" per hour"





--ron

On Wed, 3 Aug 2005 18:35:57 -0500, caliskier
<caliskier.1t7qme_1123113935.0338@excelforum-nospam.com> wrote:

>"at the rate of $"&('[RATE.xls]Rate1'!$L$954)&" per Hour"

You could use the TEXT worksheet function.

Something like:

"at the rate of "&TEXT([RATE.xls]Rate1!$L$954),"$#,##0.00")&" per Hour"

or, if you wanted the result to be a number that you could use in other cells,
you could use the formula: ='[RATE.xls]Rate1'!$L$954

and the custom format of:

"at the rate of "$#,##0.00" per hour"


Format/Cells/Number/Custom Type: "at the rate of "$#,##0.00" per hour"





--ron

On Wed, 3 Aug 2005 18:35:57 -0500, caliskier
<caliskier.1t7qme_1123113935.0338@excelforum-nospam.com> wrote:

>"at the rate of $"&('[RATE.xls]Rate1'!$L$954)&" per Hour"

You could use the TEXT worksheet function.

Something like:

"at the rate of "&TEXT([RATE.xls]Rate1!$L$954),"$#,##0.00")&" per Hour"

or, if you wanted the result to be a number that you could use in other cells,
you could use the formula: ='[RATE.xls]Rate1'!$L$954

and the custom format of:

"at the rate of "$#,##0.00" per hour"


Format/Cells/Number/Custom Type: "at the rate of "$#,##0.00" per hour"





--ron

On Wed, 3 Aug 2005 18:35:57 -0500, caliskier
<caliskier.1t7qme_1123113935.0338@excelforum-nospam.com> wrote:

>"at the rate of $"&('[RATE.xls]Rate1'!$L$954)&" per Hour"

You could use the TEXT worksheet function.

Something like:

"at the rate of "&TEXT([RATE.xls]Rate1!$L$954),"$#,##0.00")&" per Hour"

or, if you wanted the result to be a number that you could use in other cells,
you could use the formula: ='[RATE.xls]Rate1'!$L$954

and the custom format of:

"at the rate of "$#,##0.00" per hour"


Format/Cells/Number/Custom Type: "at the rate of "$#,##0.00" per hour"





--ron

On Wed, 3 Aug 2005 18:35:57 -0500, caliskier
<caliskier.1t7qme_1123113935.0338@excelforum-nospam.com> wrote:

>"at the rate of $"&('[RATE.xls]Rate1'!$L$954)&" per Hour"

You could use the TEXT worksheet function.

Something like:

"at the rate of "&TEXT([RATE.xls]Rate1!$L$954),"$#,##0.00")&" per Hour"

or, if you wanted the result to be a number that you could use in other cells,
you could use the formula: ='[RATE.xls]Rate1'!$L$954

and the custom format of:

"at the rate of "$#,##0.00" per hour"


Format/Cells/Number/Custom Type: "at the rate of "$#,##0.00" per hour"





--ron