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?
Posted by Barrie Davidson on June 08, 2001 11:32 AM
Can you clarify what you want to do by providing an example?
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.
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.
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.
Posted by Roni on June 08, 2001 12:25 PM
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"
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"
Posted by Barrie Davidson on June 08, 2001 12:47 PM
IML: No need to apologize...
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.
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" :
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".
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):
Posted by IML on June 08, 2001 1:20 PM
EURIKA!
That worked for me, too. I still can't figure why the general format and =A2-A1&" days" worked for me and not Roni.
Posted by Barrie Davidson on June 08, 2001 1:20 PM
Formula explained
Posted by Barrie Davidson on June 08, 2001 1:29 PM
Formula explained (for real this time)
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
