Excel cell, possible to have formula and text together? 1

[morningla]

I am trying to display a formula with text in the same cell, is that possible?

i tried concatenate but that only joins text.

i can't do it in the next cell, as the table format won't look right.

 

[SkipVought]

Hi,

And for what purpose: documentation?

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[morningla]

Sorry, I don't mean the formula itself,
but I need to display the result of the formula, and some text after that.

sounds simple, but i don't know where to find the correct syntax.

 

[SkipVought]

Maybe
[tt]
=Left(A1, 5)&" and other stuff."
[/tt]
???

Give me an example, please.

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[sawedoff]

Assume cell A12 contains the value 1435. Enter the following formula into another cell:
="Total: "&A12
The formula cell will display: "Total: 1435."
The ampersand (&) is a concatenation operator that joins the text with the contents of cell A12.
Applying a number format to the cell containing the formula has no effect, because the cell contains text, not a value. As a work-around, modify the formula to use the TEXT function (the second argument for the TEXT function consists of a standard Excel number-format string).
="Total: "&TEXT(A12,"$#,##0.00")
This formula will display "Total: $1,435.00."
Here's another example formula that uses the NOW function to display some text along with the current date and time:
="Report printed on "&TEXT(NOW(),"mmmm d, yyyy at h:mm AM/PM")
By the same process, If Cell B12 contained 12, the entry =(B12+5)&" Dollars" would display "17 Dollars"


Sawedoff

 

[morningla]

i got it to work thanks.

i needed something like this

=countif(D110,"0")&" Days

 

[KenWright]

Juts format the cell using Format / Cells / Number and then hit custom and put in

0 " & Days"

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

Oops - You don't want the & in the text do you, so just use

0 " Days"

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[morningla]

oh i already got it to work with this
=COUNTIF(C2:C366,"0")&" Holidays"

and it displays:
12 Holidays

exactly what I want... the 0 was my citeria. thanks!

& was pretty much the function I was looking for.
I just had my syntax wrong when I tried to put the text in the front.

"Holidays: "&=COUNTIF(C2:C366,"0") IS WRONG
="Holidays: "&COUNTIF(C2:C366,"0") IS CORRECT

 

[KenWright]

Whilst it may not matter for your data, the difference bewteen what you have done and what i suggested is that your cell now contains TEXT and cannot be used in any further calculations, whereas mine left your data as numeric and still able to be used in calculations if needed.

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[morningla]

I see. Thanks.
It worked great. Could you explain to me what the 0 means?
when I format the custom cell?

0 " Holiday(s)

 

[KenWright]

It means youe data will be displayed rounded to the nearest integer. You could also have used

0.0 " Holiday(s)"

or

0.00 " Holiday(s)"

etc

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------