vba – Bold text in a Concatenate formula

vba – Bold text in a Concatenate formula

You cannot format individual characters in a cell text if that cell contains a formula.
Excel doesnt support that.


Workaround

The only workaround is to write that cell text as constant text (with VBA) instead of a formula (if that meets your requirement).

Then you can format individual characters with:

Range(A1).Characters(Start:=1, Length:=10).Font.Bold = True

So to partly format a string you could adjust the following example

Public Sub ExampleConcatenate()
    Dim str1 As String, str2 As String, str3 As String
    str1 = First string 
    str2 = Second string 
    str3 = Third string
    
    Range(A1).Value = str1 & str2 & str3 concatenate strings
    
    format bold starts 1 character after str1 and is as long as str2
    Range(A1).Characters(Start:=Len(str1) + 1, Length:=Len(str2)).Font.Bold = True
End Sub

For more sub strings it would be easier to use an array.

Public Sub ExampleConcatenate()
    
    Dim SubStrings As Variant
    SubStrings = Array(First string , _
                       Second string , _
                       Third string , _
                       Fourth string , _
                       Fifth string , _
                       Sixth string)

    Range(A1).Value = Join(SubStrings, )

    Note array counting starts with 0 not 1 so First string is SubStrings(0)
    
    format bold starts 1 character after str1 and is as long as str2
    Range(A1).Characters(Start:=Len(SubStrings(0)) + 1, Length:=Len(SubStrings(1))).Font.Bold = True
    format sub string 4
    Range(A1).Characters(Start:=Len(SubStrings(0)) + Len(SubStrings(1)) + Len(SubStrings(2)) + 1, Length:=Len(SubStrings(3))).Font.Bold = True
    format sub string 6
    Range(A1).Characters(Start:=Len(SubStrings(0)) + Len(SubStrings(1)) + Len(SubStrings(2)) + Len(SubStrings(3)) + Len(SubStrings(4)) + 1, Length:=Len(SubStrings(5))).Font.Bold = True

End Sub

While you cannot make text bold within a formula, you CAN make text u̲n̲d̲e̲r̲l̲i̲n̲e̲d̲. No VBA needed; the magic here is unicode special characters.

After each character that you want underlined, insert the unicode combining low line character U+0332. You can copy-paste it from that site, or use this site to apply it after each character in the text you want to print.

See the example below to apply it to an arbitrary word in a text string.

Underline

=LEFT(B2,B6)&TEXTJOIN(̲,TRUE,MID(B2,ROW(OFFSET(A1,B6,0,B7)),1))&MID(B2,B6+B7,LEN(B2))

Starting with LEFT(B2,B6) returns up until the fifth word. Similarly MID(B2,B6+B7,LEN(B2)) returns everything after the fifth word.

The real magic happens with TEXTJOIN(). This function repeats the first argument in between each element of the reference in the third argument. Notice the underline is combined with the double quote character when rendered in the equation bar. The third argument uses MID() to return the fifth word, but with each letter a separate reference so that it is combined with the combining low line character by TEXTJOIN(). Splitting the fifth word by each character is achieved by passing an array as the second argument of MID() using ROW(OFFSET()) to count from the start to the end of the fifth word.

vba – Bold text in a Concatenate formula

Leave a Reply

Your email address will not be published. Required fields are marked *