Forum Discussion

Brass Contributor

Jun 18, 2021

Solved

Format numbers within a formula containing text and numbers

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

Excel commentray.xlsx9 KB

Formulas and Functions

Riny_van_Eekelen
Jun 18, 2021
JennyHoA20181 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).

4 Replies

Riny_van_Eekelen
Platinum Contributor
Jun 18, 2021
JennyHoA20181 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).
- JennyHoA20181
  Brass Contributor
  Jun 18, 2021
  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?
  - Riny_van_Eekelen
    Platinum Contributor
    Jun 18, 2021
    JennyHoA20181 Like this!
    ="Forecast: is currently tracking at "&TEXT(J1,"#,##0")&". A decline of "&TEXT(J2,"+0%;-0%")&" vs RAD target"

Resources