adding a cell in a text string

[seemore]

I was able to find the syntax to add a cell within a text string but I am having a formatting problem. The cells which I am adding were using formulas that left decimals. Although I turned off all of the decimals on the cell, the values when I used the cell within the text string included all of the decimals and in some cases 6 or more decimal places. Is there a way to keep the formatting of the cell?

Thanks in advance.

[ChemistB]

I assume you have something like

Please Login or Register  to view this content.

You need to use the TEXT Function like so

Please Login or Register  to view this content.

Does that work for you?

[seemore]

I'm not very good at this and it didn't work for me but i'm not sure i am following it correctly. The string that I wrote initially was "the answer is "&Sheet3!L18&"." I added the word text like you had it but I got an error.

[oldchippy]

Hi,

May be this

="the answer is "&ROUND(Sheet3!L18,0)&"."

[seemore]

That worked pefectly. I feel like I'm addicted now and I want more. If possible, I would like the number to show as currency and with commas. I figured out a way to get the $ sign there by just adding it as text in front of the formula so that isn't a big one, but how can I do the comma?

Thanks again.

[oldchippy]

Hi,

No need to add the $ sign, all you need to do is select the cell, right click, Format, Number, Currency

[seemore]

I had the cells already formatted as currency with $ and no decimals. I would have thought that that would have worked also and when it didn't, I simply added in the $ in the text.

[oldchippy]

Hi,

Sorry my mistake, forgot it was a text cell, so try

="the answer is $"&TEXT(ROUND(Sheet3!L18,0),"#,000")&"."

(Do you really need the decimal point at the end?)

[NBVC]

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

http://www.thecodecage.com/forumz/ex...xt-string.html

[seemore]

Wow, that is a lot of formulas but it worked perfectly. The decimal at the end was really a period for the end of the sentence. Thanks a bunch you were a great help. Inadvertently I think I figured out my $ issue. I accidently hit the $ when putting in "$#,000" and now I have commas and a $. Again many, many thanks.

[oldchippy]

Thanks for taking the time to read and comply with the rule .

[oldchippy]

Hi,

Just for the record a slightly shorter version would do, that adds the comma and rounds to thousands

="The answer is $"&FIXED(Sheet3!L18,0)&"."