[Solved] Text and formula with space in same cell

[Kenmc]

How do I add text and formula with space in same cell. This is what I have: ="Total" & (SUM(C7+C8)) and this is the result in the cell: Total233.7.Thank for any and all help.
Ken

[acknak]

Greetings and welcome to the community forum!

Now don't smack yourself ...

="Total " & (SUM(C7+C8))

Also, the SUM and + are redundant; one or the other does the same job.

[Kenmc]

Thanks acknak,
If this is your answer

Now don't smack yourself ...

="Total " & (SUM(C7+C8))

It's the same as the one in my post.

[acknak]

Not quite ...

="Total" & (SUM(C7+C8))
="Total " & (SUM(C7+C8))

[Kenmc]

Sorry I see the difference.

[Kenmc]

Sorry I see the difference.

[Kenmc]

Is there anyway to change the numerical valve to currency and make the text bold?

[Zizi64]

Is there anyway to change the numerical valve to currency and make the text bold?

"valve" is "value" really, yes?

You can not apply different Styles (by a formula) for parts of a cell content.
But you can use two cells in a row to display this combination.

[Kenmc]

Thank all for the help.

[RusselB]

Contrary to Tibor's post, you can get the value to appear as a formatted number by using the TEXT function.
Thus you would have something like

Code: Select all

="Total " & text(sum(c7;c8);"$###.#0")

See the help file for TEXT function and Number Format Codes

[Kenmc]

Thank you

[Kenmc]

I did a search on TEXT formatting and I could not find anything. I am new to spreadsheets.

[acknak]

TEXT uses the same number format codes as the spreadsheet cells (Format > Cells > Numbers ...)

You can see a list of the codes in the online help: Help > OO Help > Index > "format codes;numbers"

They're also described in the LibreOffice help, which is available on the web: https://help.libreoffice.org/Common/Number_Format_Codes

[Kenmc]

Thanks,
This doesn't tell me how to make the text bold and level the numbers normal.

[RusselB]

Sorry, guess I misunderstood what you were wanting.
With your latest post I have to say that I don't think it can be done and maintain the information in a single cell.
One possibility you might try is to have the information in to separate cells, then use a concatenation operation to combine into a single cell.

[Kenmc]

Thanks

[acknak]

Oops, I misunderstood what you mean by "format"

... This doesn't tell me how to make the text bold and level the numbers normal.

Sorry, this is not possible.

The result of a formula can have only one text format, which will apply to the whole contents of the cell.

Only text that you enter can have complex text formatting.

If you want the label in bold and the number value not bold, you have to keep them in separate cells, as Russell already suggested.

[Kenmc]

Thank you again.

[keme]

With the above approach, the cell value is text which cannot be reliably used in further calculations. (It may be useful if you want to use the entire "Total xxx" string as text, like in a mail merge context.)

The common solution is to use cell formatting instead:

• Format - Cell..., tab Numbers.
  In the Format code field, enter: "Total: " $ # ##0.00

This way, you get the appearance you ask for within one cell, but the cell value is numerical so it can be used for further calculations. Alas, there is still no possibility to emphasize parts of what is displayed. For that you still need to split to separate cells.

[bryan.hoffmann]

Excellent - this helped enormously, thanks!
Note, to get eg 'total cost is €12' in one cell, this also works: ="total cost is €"&SUM(I31:I57)