How to Bold Text in a Concatenate Formula using VBA in Excel

This is the sample dataset.

Method 1 – Bold Text in the Output of the Concatenate Formula.

Press ALT+F11 to open VBA.
In the VBA window, click the Insert tab and select Module.

The Module(Code) window will open.

Enter the following code.

Public Sub Bold_in_Concatenate()

    Dim FN As String, LN As String
    FN = Range("B5")
    LN = Range("C5")
    Range("D13").Value = FN & " " & LN
    Range("D5").Font.Bold = False
    Range("D5").Characters(Start:=1, 
    Length:=Len(FN)).Font.Bold = True

End Sub

The code will create a Macro (Bold_in_Concatenate) which will join the text of B5 and C5 and return the output in D5. It will also bold the text imported from B5.

Close or minimize the VBA window.
Press ALT+F8 to open the Macro.
Select Bold_in_Concatenate in the Macro name box and click Run.

The output of the concatenate formula will be displayed in D5.

By changing the cell reference, you can get a similar result for other rows. For Row 6:

Change 5 in the cell reference into 6.

Enter the following code:

Public Sub Bold_in_Concatenate()

    Dim FN As String, LN As String
    FN = Range("B5")
    LN = Range("C5")
    Range("D13").Value = FN & " " & LN
    Range("D5").Font.Bold = False
    Range("D5").Characters(Start:=1, 
    Length:=Len(FN)).Font.Bold = True

End Sub

Click Run or press F5.

Minimize or close the VBA window.

B6 and C6 are concatenated into D6 and a part of B6 is bold.

You can bold text in a concatenate formula for other rows.

Method 2 – Bold Text in a Concatenate Formula for a Range

You can also create a Macro to bold text in a concatenate formula for a data range.

2.1. For Specific Text

You want to bold text of column B in the concatenate formula of column C:

Press ALT+F11 to open the VBA window.
Click the Insert tab and select Module.

The Module(Code) window will open.

Enter the following code:

Sub Bold_in_Concatenate_range()

    Dim NRng As Range
    Dim NTx As String
    Dim NCell As Range
    Dim I As Long
    On Error Resume Next
    If ActiveWindow.RangeSelection.Count > 1 Then
      NTx = ActiveWindow.RangeSelection.AddressLocal
    Else
      NTx = ActiveSheet.UsedRange.AddressLocal
    End If
LInput:
    Set NRng = Application.InputBox("Select a data range:", "Bold First column in Concatenate", NTx, , , , , 8)
    If NRng Is Nothing Then Exit Sub
    If NRng.Areas.Count > 1 Then
        MsgBox "does not support multiple selections"
        GoTo LInput
    End If
    If NRng.Columns.Count <> 2 Then
        MsgBox "only two columns in the selection"
        GoTo LInput
    End If
    Set NRng = NRng.Resize(NRng.Rows, 3)
    On Error Resume Next
    For Each NCell In NRng.Columns(3).Cells
        NCell = NRng.Cells(NCell.Row, 1) & " " & NRng.Cells(NCell.Row, 2)
        NCell.Font.Bold = False
        NCell.Characters(1, Len(NRng.Cells(NCell.Row, 1))).Font.FontStyle = "Bold"
    Next

End Sub

The formula will create a Macro. You can input a range of two columns and it will return the concatenate of those columns with bold text from the first column.

Close or minimize the VBA window.
Press ALT+F8.

The Macro window will open.

Select Bold_in_Concatenate_range in the Macro name box and click Run.

An input box (Bold First column in Concatenate) will be displayed.

Select the columns you want to concatenate and click OK.

In column 3 you will get the concatenate of columns 1 and 2. The text imported from column 1 will be bold.

2.2. For the Entire Text

Enter the following code in the Module(Code) window.

Sub Bold_in_Concatenate_range()

    Dim NRng As Range
    Dim NTx As String
    Dim NCell As Range
    Dim I As Long
    On Error Resume Next
    If ActiveWindow.RangeSelection.Count > 1 Then
      NTx = ActiveWindow.RangeSelection.AddressLocal
    Else
      NTx = ActiveSheet.UsedRange.AddressLocal
    End If
LInput:
    Set NRng = Application.InputBox("Please select the data range:", "Bold All text in Concatenate", NTx, , , , , 8)
    If NRng Is Nothing Then Exit Sub
    If NRng.Areas.Count > 1 Then
        MsgBox "does not support multiple selections"
        GoTo LInput
    End If
    If NRng.Columns.Count <> 2 Then
        MsgBox "only two columns in the selection"
        GoTo LInput
    End If
    Set NRng = NRng.Resize(NRng.Rows, 3)
    On Error Resume Next
    For Each NCell In NRng.Columns(3).Cells
        NCell = NRng.Cells(NCell.Row, 1) & " " & NRng.Cells(NCell.Row, 2)
        NCell.Font.Bold = True
         Next

End Sub

Close or minimize the VBA window.
Press ALT+F8.

The Macro window will open.

Select Bold_in_Concatenate_range in the Macro name box and click Run.

An input box (Bold All Text in Concatenate) will be displayed.

Select the columns you want to concatenate and click OK.

You will see the entire bold text in the concatenate formula.

Download Practice Workbook

Bold Text in Concatenate Formula.xlsm

<< Go Back to Concatenate Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Agnes May 19, 2023 at 1:09 PM

I need this for Google Sheets. please mail me for update
- Reply
  Seemanto Saha May 22, 2023 at 10:49 AM
  Dear Agnes,
  
  To obtain similar results in Google Sheets, we have to create a similar dataset, click on the Extensions menu, and select Apps Script from the options.
  
  Then in the new window, we have to replace the default script with the following script:
```
function boldTextInConcatenate() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  .getSheetByName("Bold in Concatenate");
 
  var lastRow = ss.getLastRow();


  var bold = SpreadsheetApp.newTextStyle()
  .setBold(true)
  .build();


  for (var i=3;i<=lastRow;i++){
    var fName = ss.getRange(i,2).getValue();
    var lName = ss.getRange(i,3).getValue();
   
    var richText = SpreadsheetApp.newRichTextValue()
    .setText(fName+' '+lName)
    .setTextStyle(0, fName.length, bold)
    .build();
   
    ss.getRange(i,4).setRichTextValue(richText);
  }
}
```
  Afterward, we have to Save and Run the script. The output should be like the following:
  
  You can download the Spreadsheet from the link below:
  Bold text in Concatenate
  
  Let us know your feedback.
  
  Regards,
  Seemanto Saha
  ExcelDemy