adding a cell in a text string

[Seemore]

I wanted to add a cell (formula) within a text string. I found the
syntax but when I did it, the cells would leave values with decimal
points. My original cells were formatted with no decimals (whole
numbers only) but inside the text string I get decimals and in some
cases 6 or more. How can I get rid of the decimals?

Hopefully this made sense. Thanks in advance for your help.

 

[Rick Rothstein]

If this is a VB question (you posted your question in a programming
newsgroup), show us your code. If this is a worksheet formula question, then
show us your formula.

 

[Seemore]

If this is a VB question (you posted your question in a programming
newsgroup), show us your code. If this is a worksheet formula question, then
show us your formula.

--
Rick (MVP - Excel)







- Show quoted text -

The string that i used is ="If you pay $Sheet3!$J$5&" over 15 years,
you will have paid a total of $"&Sheet3!L18&"." The first cell
gives me 2 decimals and the second gives me 9. I would prefer whole
numbers instead. I appreciate the help and will also seek the help of
the other group you mentioned.

Thanks again for your help.

 

[Don Guillett]

try int(rng)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

If this is a VB question (you posted your question in a programming
newsgroup), show us your code. If this is a worksheet formula question,
then
show us your formula.

--
Rick (MVP - Excel)







- Show quoted text -

The string that i used is ="If you pay $Sheet3!$J$5&" over 15 years,
you will have paid a total of $"&Sheet3!L18&"." The first cell
gives me 2 decimals and the second gives me 9. I would prefer whole
numbers instead. I appreciate the help and will also seek the help of
the other group you mentioned.

Thanks again for your help.

 

[Seemore]

try int(rng)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software




The string that i used is ="If you pay $Sheet3!$J$5&" over 15 years,
you will have paid a total of $"&Sheet3!L18&"."    The first cell
gives me 2 decimals and the second gives me 9.  I would prefer whole
numbers instead.  I appreciate the help and will also seek the help of
the other group you mentioned.

Thanks again for your help.- Hide quoted text -

- Show quoted text -

Thanks. Int seemed to have gotten rid of the decimals. The number
however does not have any commas nor a $. The original cell is
formatted for currency and no decimals. I was able to overcome the $
issue by just adding it as text which works but commas would be very
helpful.

Thanks.

 

[Don Guillett]

$1,026

="ddddddddddddd "& TEXT(O7,"$#,##0")& " dddddddd" &
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

try int(rng)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software




The string that i used is ="If you pay $Sheet3!$J$5&" over 15 years,
you will have paid a total of $"&Sheet3!L18&"." The first cell
gives me 2 decimals and the second gives me 9. I would prefer whole
numbers instead. I appreciate the help and will also seek the help of
the other group you mentioned.

Thanks again for your help.- Hide quoted text -

- Show quoted text -

Thanks. Int seemed to have gotten rid of the decimals. The number
however does not have any commas nor a $. The original cell is
formatted for currency and no decimals. I was able to overcome the $
issue by just adding it as text which works but commas would be very
helpful.

Thanks.

 

[Ron Rosenfeld]

The string that i used is ="If you pay $Sheet3!$J$5&" over 15 years,
you will have paid a total of $"&Sheet3!L18&"." The first cell
gives me 2 decimals and the second gives me 9. I would prefer whole
numbers instead. I appreciate the help and will also seek the help of
the other group you mentioned.

You have to do the formatting within the text statement.

You probably want something like:

="If you pay "&TEXT(Sheet3!$J$5,"$#,##0")
&" over 15 years, you will have paid a total of "&
TEXT(Sheet3!L18,"$#,##0.")

The above formatting assumed you wanted zero decimal places, $ formatting
(select your own currency symbol if '$' is not appropriate), and that there was
nothing special to do about negative values.
--ron

 

[Seemore]

You have to do the formatting within the text statement.

You probably want something like:

="If you pay "&TEXT(Sheet3!$J$5,"$#,##0")
&" over 15 years, you will have paid a total of "&
TEXT(Sheet3!L18,"$#,##0.")

The above formatting assumed you wanted zero decimal places, $ formatting
(select your own currency symbol if '$' is not appropriate), and that there was
nothing special to do about negative values.
--ron

That worked perfectly. In the future, if I decide I want decimals how
would I change it? Again, many thanks. These forums have saved me a
lot of headaches.

 

[Ron Rosenfeld]

That worked perfectly. In the future, if I decide I want decimals how
would I change it? Again, many thanks. These forums have saved me a
lot of headaches.

Take a look at HELP for the TEXT worksheet function and also for "Number Format
Codes".

The format code you would use in the TEXT function is the same code you would
use if you custom formatted a cell (Format/Cells/Number/Custom Type: )

However, I used a little trick to put in the terminal ".", so it may not be
clear to you.

To redo the sentence to allow, for example, two decimals:

="If you pay "&TEXT(Sheet3!$J$5,"$#,##0.00")
&" over 15 years, you will have paid a total of "&
TEXT(Sheet3!L18,"$#,##0.00\.")

--ron