Bold text in a Concatenate formula

Question

I would like to be able to make one or two specific words bold in my sentence when using a concatenate formula. An example is shown below.

The first sentence is using the concatenate formula. The second sentence is manually typed and formatted. Is there a way to have this formatting in the concatenate formula without having to do it manually every time?

Please note that this is just an example and I may need to use it to make a string of three consecutive words bold in a different sentence. If a general rule was provided that I can work with going forwards that would be great!

I am somewhat proficient with formulae but have never used VBA. However I suspect the solution for this problem may only be available using VBA. Please be considerate if a VBA solution is required as it'll take some time and effort for me to start-up and understand.

Thanks for your time and help.

Edit:

Public Sub ExampleConcatenate()
    Dim str1 As String, str2 As String, str3 As String, str4 As String, str5 As String, str6 As String
    str1 = "First string "
    str2 = "Second string "
    str3 = "Third string"
    str4 = "Fourth string "
    str5 = "Fifth string "
    str6 = "Sixth string"


    Range("A1").Value = str1 & str2 & str3 & str4 & str5 & str6 '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

How would I further extend the final part to make the fourth and sixth strings bold?

Answer 1

You cannot format individual characters in a cell text if that cell contains a formula.
Excel doesn't 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