Formatting lost when linked to text

[Boniouk]

So whilst the answer may be complicated, the problem is pretty simple...

I have a cell (A1) with a value, such as -2%.

The cell is formatted and will show underlined, red and bold.

I then have another cell with this formula. ="The latest week result is "&A1

but my result shows as...

The latest week result is -1.989877434%


Is there anyway to format that section to make sure its also red, bold, underlined and decimal places?

 

[mole999]

I'm pretty sure its not the fact that you are linking to a cell and adding text that's defeating your formatting.

You might create a CF rule that says if A1 <0 then add a bold, underlined red font to this cell

 

[Boniouk]

I cannot use CF when highlighting my new cell. Also, I don't want the whole cell including text formatted. Just the result at the end of the text string.

 

[mole999]

closest I could come to it

Custom Format

"The latest week result is "#,##0%;[Red]"The latest week result is" -#,##0%

Maybe two cells close together, one with text and the other with the formatted value

 

[Boniouk]

Thanks Mole, that's pretty close. To be honest don't think it's possible to do what I need.

I wanted it all within one string so that the cell could be referenced in a text box. That way, the automation from result, to text to text box means the text box can be copied and pasted each week into a powerpoint pack. Automating the conditional formatting from a cell into a text string and then into a textbox, all whilst keeping formatting does appear to be.... impossible.

 

[mole999]

issues with formats being copied out, only reproducible way that works was to copy to word from excel in the past for me