SOLVED

Format numbers within a formula containing text and numbers

Contributor

Hello,

 

How do I format numbers within a formula containing text and numbers, to look exactly as they appear in cells J1 and J2 in the attached excel?

 

For example I want the sentence below:

Forecast: is currently tracking at 1230. A decline of -0.09 vs RAD target

 

To look like this:

Forecast: is currently tracking at 1,230. A decline of -9% vs RAD target

 

Thanks!

 

Jenny

4 Replies
best response confirmed by JennySommet (Contributor)
Solution

@JennySommet Adjust your formula to this:

="Forecast: is currently tracking at "&TEXT(J1,"#,##0")&". A decline of "&TEXT(J2,"0%")&" vs RAD target"

It will correctly display the number format, but as far as I know, it's not possible to set the font format (bold and red).

Great! Thank you! This helps a lot, I will do the Bold and colour manually in that case. Is it possible to add a + in the formula if the percentage / number is positive?

@JennySommet Like this!

="Forecast: is currently tracking at "&TEXT(J1,"#,##0")&". A decline of "&TEXT(J2,"+0%;-0%")&" vs RAD target"

 

Thank you very much!