Format part of a formula result… A workaround (non-VBA) solution

How to format part of a formula result?

In a normal text string, we may format part of the text to highlight a specific information.  For example, if we input the text string “Today is Thursday, 01/05/2014” in A1, we could  BOLD part of the text string by selecting the text string in the formula bar and then apply the format required.


“Today is Thursday, 01/05/2014” is displayed in A1 as a result.

Sometimes we may use CONCATENATE function or simply “&” to join a text string with a result of a formula.  For Example, if we would like to display Today in form of a text string, say, “Today is Thursday, 01/05/2014” in A1, we can not simply input =today() that gives on 01/05/2014 in A1. Instead, we need

="Today is " & TEXT(TODAY(),"DDDD, DD/MM/YYYY")

Tips: “Today is ” & A1 returns “Today is 41760”, where “41760” is the series number representing 01/05/2014 which is the 41760th day from January 1, 1900. This is how Excel stores date. To get the desired result, we need the TEXT function to convert the date into the specified format required.

We wish we could format the formula in a way that yields a formatted result:

="Today is " & TEXT(TODAY(),"DDDD, DD/MM/YYYY")
==> "Today is Thursday, 01/05/2014" in A1.

However, there is no such an easy way.  I am not sure if VBA can achieve that as I am not good in VBA.

So I am going to show you a workaround.  Although it is not an elegant solution, it should do the trick.  🙂

Let’s start with an example:


In this example, A1 and MAX(B3:B5) are the variables we want to format in the text string.

Let’s do the tricks step by step:

1) Input a text string like the one below.

ImageNote: Leave appropriate empty SPACE in the text string.

2) Insert a Text Box.  (Go to Insert Tab –> Text Group –> Text Box)


3) Instead of inputting text directly to the text box, give a cell reference to it.

Select the text box –> click inside the formula bar –> Input =$A$1 (or use mouse cursor to click on A1)


4) Now the text box is linked to the content in A1


5) Repeat step 3 for “the highest score”.

Note: We cannot assign a formula to a text box directly.  Therefore we need a helper cell – G1 where we input the formula =MAX(B3:B5).  We may hide the helper column then.


6) Format the two text boxes in the way we want, e.g. BOLD the Week and turn the highest score into RED


7) Move the text box to the appropriate position.  Align them nicely.


8) Here we go!


Yes, I know.  They are not perfect.  If you have a better non-VBA solution, please share in the comment. 🙂

This entry was posted in Excel Tips, Format and tagged , . Bookmark the permalink.

6 Responses to Format part of a formula result… A workaround (non-VBA) solution

  1. E says:

    It’s not only non-VBA, it’s also non-formula. The whole point is that any “formula result” is within a single cell. Splitting the results into separate cells just to achieve formatting? It’s not a solution or a workaround.


  2. Pingback: How to format only a part of a formula in Excel2007

  3. Pingback: How to bold a single word in a text string that appears in an excel IF statement

  4. Reymark Lazo says:

    Maybe you can use custom formatting options?


    • MF says:

      Hi Reymark, thanks for your comment. I didn’t know how it could be done by using custom formatting. Appreciate if you could share with us how.


  5. Pingback: Bold text within formula output

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s