Font weight BOLD using formula in cell

Question

I'm using Google Spreadsheet to make some calculations.

At the bottom of my table I need SUM, AVG and some others and everything is fine.

But I made a long cell with all the text, like this:

="Hi at all, this is my report: "&SUM(B:B)&" are the sum of my fingers, "&AVG(C:C)&" is the avg of my sons."

and so on.

Everything works. But I need to bold the SUM(B:B).

I won't use single cells for math results.

I tried with ....<b>"&SUM(B:B)&"</b>.... but obviously I take <b></b> in my cell and not the bold font weight.

How to style from formula in cell?

Answer 1

Recently I had the same problem, but I found a solution. This solution works well in google sheets.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E3,"A","𝐀"),"B","𝐁"),"C","𝐂"),"D","𝐃"),"E","𝐄"),"F","𝐅"),"G","𝐆"),"H","𝐇"),"I","𝐈"),"J","𝐉"),"K","𝐊"),"L","𝐋"),"M","𝐌"),"N","𝐍"),"O","𝐎"),"P","𝐏"),"Q","𝐐"),"R","𝐑"),"S","𝐒"),"T","𝐓"),"U","𝐔"),"V","𝐕"),"W","𝐖"),"X","𝐗"),"Y","𝐘"),"Z","𝐙"),"0","𝟎"),"1","𝟏"),"2","𝟐"),"3","𝟑"),"4","𝟒"),"5","𝟓"),"6","𝟔"),"7","𝟕"),"8","𝟖"),"9","𝟗")

If you want to improve your formula, use the characters below:

ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789abcdefghijklmnopqrstuvwxyz
𝐀𝐁𝐂𝐃𝐄𝐅𝐆𝐇𝐈𝐉𝐊𝐋𝐌𝐍𝐎𝐏𝐐𝐑𝐒𝐓𝐔𝐕𝐖𝐗𝐘𝐙𝟏𝟐𝟑𝟒𝟓𝟔𝟕𝟖𝟗𝐚𝐛𝐜𝐝𝐞𝐟𝐠𝐡𝐢𝐣𝐤𝐥𝐦𝐧𝐨𝐩𝐪𝐫𝐬𝐭𝐮𝐯𝐰𝐱𝐲𝐳

Answer 2

At the current time, in Google Sheets you cannot bold partial components of any formula. The solution mentioned using the 'B' or bold icon only works if the desired bold selection is an entire cell containing a formula, or partial of a cell that does not contain a formula.

Answer 3

Having the same issue at the moment. Spent a decent amount of time figuring it out before I realized it cannot be done. The reason for this is that Google spreadsheets does not allow bits of a query to have bold/italics/underline to it.

The only workaround I have found is to do it manually . Let the query run , and highlight the section of the string you want with a different formatting option.

Answer 4

To make any of the cells bold just make a conditional format for the cell/range/array and choose bold.

Answer 5

Yes, this is possible.

---

My own solution is at the bottom, but I find the one added by Adrian Mihai Nemes here to be cleaner. So, I have expanded that solution here to work for lowercase as well as uppercase and numbers. This should work for text containing anything, including newlines, single quotes, emojis, etc.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B25,"A","𝐀"),"B","𝐁"),"C","𝐂"),"D","𝐃"),"E","𝐄"),"F","𝐅"),"G","𝐆"),"H","𝐇"),"I","𝐈"),"J","𝐉"),"K","𝐊"),"L","𝐋"),"M","𝐌"),"N","𝐍"),"O","𝐎"),"P","𝐏"),"Q","𝐐"),"R","𝐑"),"S","𝐒"),"T","𝐓"),"U","𝐔"),"V","𝐕"),"W","𝐖"),"X","𝐗"),"Y","𝐘"),"Z","𝐙"),"a","𝐚"),"b","𝐛"),"c","𝐜"),"d","𝐝"),"e","𝐞"),"f","𝐟"),"g","𝐠"),"h","𝐡"),"i","𝐢"),"j","𝐣"),"k","𝐤"),"l","𝐥"),"m","𝐦"),"n","𝐧"),"o","𝐨"),"p","𝐩"),"q","𝐪"),"r","𝐫"),"s","𝐬"),"t","𝐭"),"u","𝐮"),"v","𝐯"),"w","𝐰"),"x","𝐱"),"y","𝐲"),"z","𝐳"),"0","𝟎"),"1","𝟏"),"2","𝟐"),"3","𝟑"),"4","𝟒"),"5","𝟓"),"6","𝟔"),"7","𝟕"),"8","𝟖"),"9","𝟗")

---

For my own solution, this will bold all lowercase, uppercase, and numbers. It should work for text containing just about anything, including newlines, single quotes, etc. (perhaps not emojis?).

Just replace the single A2 reference with whatever it is you want bolded:

=ARRAYFORMULA(JOIN("", UNICHAR(QUERY(UNICODE(SPLIT(TRANSPOSE(SPLIT(
    REGEXREPLACE(
        REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(A2&""
            ,"([^a-zA-Z0-9])","$1"&UNICHAR(160)&UNICHAR(1)&CHAR(127))
            ,"'","''")
            ,"([a-z])","$1"&UNICHAR(160)&UNICHAR(119738)&CHAR(127))
            ,"([A-Z])","$1"&UNICHAR(160)&UNICHAR(119744)&CHAR(127))
            ,"([0-9])","$1"&UNICHAR(160)&UNICHAR(120735)&CHAR(127))
    ,"'","''")
,CHAR(127))), UNICHAR(160))), "select Col1+Col2-1 label Col1+Col2-1 ''",0))))

Overview: For each character group (lowercase, uppercase, numbers) we're using stringed together REGEXREPLACE calls to append special separator characters along with the base unicode character for that group's bold font right after.

So for example, Hi-1 becomes "H"&UNICHAR(160)&UNICHAR(119743)&CHAR(127)&"i"&UNICHAR(160)&UNICHAR(119737)&CHAR(127)&"-"&UNICHAR(160)&UNICHAR(1)&CHAR(127)&"1"&UNICHAR(160)&UNICHAR(120734)&CHAR(127).

Once we have this new string, we split on the `CHAR(127) and transform, so each of these characters are on their own row. So the example now becomes:

"H"&UNICHAR(160)&UNICHAR(119743)
"i"&UNICHAR(160)&UNICHAR(119737)
"-"&UNICHAR(160)&UNICHAR(1)
"1"&UNICHAR(160)&UNICHAR(120734)

Next, we split on the UNICHAR(160) character:

"H", UNICHAR(119743)
"i", UNICHAR(119737)
"-", UNICHAR(1)
"1", UNICHAR(120734)

We use the UNICODE() function to convert the actual characters along with their corresponding UNICHAR into their unicode numbers:

72, 119744
105, 119738
45, 1
49, 120735

Now, we use the QUERY() function as a way of summing each of these rows individually. That's where the "select Col1+Col2-1 label Col1+Col2-1 ''" comes in. It is adding column 1 to column 2 and taking away the 1 extra from the base unicode value, and then preventing a heading label from being added to the function output. So, now we get:

119815
119842
45
120783

We use the next UNICHAR() function to convert these to their unicode characters, which at this point is the corresponding bolded character:

𝐇
𝐢
-
𝟏

Lastly, we use a JOIN() with an empty string "" delimiter to combine it all back into a single string.

𝐇𝐢-𝟏

p.s. If you're curious why the different character groups need to be split up, it's because each group lines up with their corresponding bold characters in order, but not all 3 character groups in a row. There are some extra characters between the normal type groups that you don't see in the bold section of unicode. Thus, each character group has to be given its own base unicode value to be added to that normal character's unicode value.

p.p.s. If you wanted to add more characters, you would just need to add another wrapping REGEXREPLACE() with the correct character group, and the UNICHAR() with the correct base unicode value for that group, and then add that new group to the exclusions from the first REGEXREPLACE(). Happy to explain this further if required.

p.p.p.s. The REGEXREPLACE() with the single quote ' being replaced with two single quotes '' is needed because when we split the characters to their own cells, Google Sheets actually considers a leading single quote as a special character and removes it. So effectively, two single quotes get converted to one single quote after splitting.