Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

Text and Formula in same cell

Posted by Roni Gardner on June 08, 2001 11:27 AM
I want to put words in a cell following the calculated formula.
Is this possible?

Check out our Excel Resources

Re: Text and Formula in same cell

Posted by Barrie Davidson on June 08, 2001 11:32 AM

Can you clarify what you want to do by providing an example?


Re: Text and Formula in same cell

Posted by IML on June 08, 2001 11:37 AM


if A1 and A2 each had 1 in them, then this

=A1+A2&" two"

would return "2 two"

good luck.



Re: Text and Formula in same cell

Posted by Roni on June 08, 2001 12:04 PM
I understand the adding text formula, but now I have
another issue...same sheet.

I have a formula that calculates the difference (in days)
between two dates. After that calculates, I want the word
"Days" to appear.

The first follow-up suggested &"text" after formula,
which worked brilliantly (thank you), however since the
number, the calculation is a DATE string, it's very very
long and I can't seem to find a format that will cut it
off two after the decimal.


Re: Text and Formula in same cell

Posted by IML on June 08, 2001 12:21 PM
When I use

=+A2-A1&" days"

and the general formal, I think I'm getting the results you are looking for. For example, with 5/25/01 and 5/28/01 in A1 and A2, I get "3 Days" as a result. What is your formula?

Sorry if I'm jumping in on you, Barry.

Good luck.


Re: Text and Formula in same cell

Posted by Roni on June 08, 2001 12:25 PM

Re: Text and Formula in same cell

Posted by IML on June 08, 2001 12:32 PM
Very strange, that works for me. You could try the formula =A7-B8 and use the custom format

d "days"


=A7-B8&" days"


Re: Text and Formula in same cell

Posted by IML on June 08, 2001 12:46 PM
That custom format won't work. It doesn't count correctly over more than one month. Sorry, I'm fresh out of ideas. Very strange, that works for me. You could try the formula =A7-B8 and use the custom format d "days"


IML: No need to apologize...

Posted by Barrie Davidson on June 08, 2001 12:47 PM
It's great to see a problem solved (I frequently learn just as much as the person asking the question), but thanks for the consideration.

Barrie When I use =+A2-A1&" days" and the general formal, I think I'm getting the results you are looking for. For example, with 5/25/01 and 5/28/01 in A1 and A2, I get "3 Days" as a result. What is your formula? Sorry if I'm jumping in on you, Barry. Good luck.


Re: Text and Formula in same cell

Posted by Barrie Davidson on June 08, 2001 12:50 PM
You could try (I think it will format the number part the way you want it):
=TEXT(A7-B8,"#")&" days"

Barrie That custom format won't work. It doesn't count correctly over more than one month. Sorry, I'm fresh out of ideas. : Very strange, that works for me. You could try the formula =A7-B8 and use the custom format : d "days" :


Re: Text and Formula in same cell

Posted by SNassau626 on June 08, 2001 1:02 PM
I'm fairly new at this, but since you CAN get the numerical value you're looking for but can't add the text "days" (if i understand you correctly), why not let the calculation go into a hidden cell (ie- D1) and then have cell E1= D1 & " Days".


Re: Text and Formula in same cell

Posted by Roni on June 08, 2001 1:17 PM
that worked. thank you so much. although i don't understand
why or how or what it did. what does "#" signify?

Thanks again for everyone's help. You could try (I think it will format the number part the way you want it):



EURIKA!

Posted by IML on June 08, 2001 1:20 PM
That worked for me, too. I still can't figure why the general format and =A2-A1&" days" worked for me and not Roni.


Formula explained

Posted by Barrie Davidson on June 08, 2001 1:20 PM



Formula explained (for real this time)

Posted by Barrie Davidson on June 08, 2001 1:29 PM
Accidently hit enter in the first post. The formula converts numbers to text. The syntax is:
=Text(value,number format)
so the "#" tells Excel to format the number as a number with no decimals and not commas. Try playing with the format (use "#,###.0" for example).

Barrie :) that worked. thank you so much. although i don't understand



This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.