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.

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)


"Seemore" wrote in message
...
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.

On Aug 10, 4:05*pm, "Rick Rothstein"
wrote:
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)

"Seemore" wrote in message

...



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.- Hide quoted text -

- 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.

try int(rng)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Seemore" wrote in message
...
On Aug 10, 4:05 pm, "Rick Rothstein"
wrote:
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)

"Seemore" wrote in message

...



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.- Hide quoted
text -

- 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.

On Aug 11, 9:45*am, "Don Guillett" wrote:
try int(rng)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Seemore" wrote in message

...
On Aug 10, 4:05 pm, "Rick Rothstein"





wrote:
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)

"Seemore" wrote in message

....

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.- Hide quoted
text -

- 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.- 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.

$1,026

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

"Seemore" wrote in message
...
On Aug 11, 9:45 am, "Don Guillett" wrote:
try int(rng)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Seemore" wrote in message

...
On Aug 10, 4:05 pm, "Rick Rothstein"





wrote:
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)

"Seemore" wrote in message

...

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.- Hide
quoted
text -

- 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.- 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.

On Tue, 11 Aug 2009 06:23:18 -0700 (PDT), Seemore
wrote:

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

On Aug 11, 10:42*am, Ron Rosenfeld wrote:
On Tue, 11 Aug 2009 06:23:18 -0700
wrote:

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

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.

On Tue, 11 Aug 2009 08:50:50 -0700 (PDT), Seemore
wrote:

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