How to Bold Text in Concatenate Formula in Excel (2 Methods)

Get FREE Advanced Excel Exercises with Solutions!

With Excel CONCATENATE function you can join two or more texts altogether. But unfortunately, there is no way to bold text in Concatenate formula directly. But you can bold text in concatenate formula by creating a Macro using Microsoft Visual Basic Application (VBA). In this article, I’ll show you how to bold text in concatenate formula in Excel.

Let’s say you have the following dataset where the first name and last name of some people are stored. Now, you want to concatenate the first and the last name and bold the first name while applying the concatenate formula.

Table of Contents hide

Download Practice Workbook

Bold Text in Concatenate Formula by Using VBA in Excel

1. Bold Text in the Output of Concatenate Formula

2. Bold Text in Concatenate Formula for a Range

2.1. For Specific Text

2.2. For Entire Text

Conclusion

Download Practice Workbook

Bold Text in Concatenate Formula.xlsm

Bold Text in Concatenate Formula by Using VBA in Excel

1. Bold Text in the Output of Concatenate Formula

In the beginning,

➤ Press ALT+F11 to open the VBA window.

In the VBA window,

➤ Click on Insert tab and select Module.

It will open the Module(Code) window. Now,

➤ Insert the following code in the Module(Code) window,

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 named Bold_in_Concatenate which will join the text of cells B5 and C5 and return the output in cell D5. It will also bold the text imported from cell B5.

After that,

➤ Close or minimize the VBA window.

➤ Press ALT+F8

It will open the Macro window.

➤ Select Bold_in_Concatenate in the Macro name box and click on Run.

As a result, you will get the output of the concatenate formula in cell D5 where you can see the part of the first name has been bold.

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

➤ Change the number 5 in the cell reference of the code into 6.

Your code will look like this,

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

After changing the cell reference,

➤ Click on the Run icon or press F5.

Now,

➤ Minimize or close the VBA window.

You will see cells B6 and C6 are concatenated into cell D6 where the part from cell B6 is bold.

In a similar way, you can get bold text in concatenate formula for all other rows.

Similar Readings

2. Bold Text in Concatenate Formula for a Range

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

2.1. For Specific Text

Suppose, you want to bold text of the column B in the concatenate formula of column C.

First,

➤ Press ALT+F11 to open the VBA window.

In the VBA window,

➤ Click on the Insert tab and select Module.

It will open the Module(Code) window. Now,

➤ Insert 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("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 where you can input a range of two columns and the output will be the concatenate of those columns with bold text of the first column.

After that,

➤ Close or minimize the VBA window.

➤ Press ALT+F8

It will open the Macro window.

➤ Select Bold_in_Concatenate_range in the Macro name box and click on Run.

As a result, an input box named Bold First column in Concatenate will appear. Here,

➤ Select the columns which you want to concatenate and press on OK.

As a result, in column 3, you will get the concatenate of columns 1 and 2 while the text imported from column 1 will be bold.

2.2. For Entire Text

Let’s say this time you want to bold the entire text in the concatenate formula. For doing this, you need to modify the code. This time,

➤ Insert 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

After that,

➤ Close or minimize the VBA window.

➤ Press ALT+F8

It will open the Macro window.

➤ Select Bold_in_Concatenate_range in the Macro name box and click on Run.

As a result, an input box named Bold All Text in Concatenate will appear. Here,

➤ Select the columns which you want to concatenate and press on OK.

As a result, you will get the entire text bold in the concatenate formula.

Conclusion

I hope now you know how to bold text in concatenate formula in Excel. With the first method of the article, you will be bold text in concatenate formula for a single output at a time. But with the second method, you will be bold text in concatenate formula for a range. If you have any confusion about any of these two methods, please feel free to leave a comment.

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

How to Bold Text in Concatenate Formula in Excel (2 Methods)

Download Practice Workbook

Bold Text in Concatenate Formula by Using VBA in Excel

1. Bold Text in the Output of Concatenate Formula

2. Bold Text in Concatenate Formula for a Range

2.1. For Specific Text

2.2. For Entire Text

Conclusion

Related Articles

Prantick Bala

How to Make a Schedule for Employees in Excel (3 Types)

How to Count Number of Columns in Excel (5 Suitable Examples)

How to Make an Availability Schedule in Excel (with Easy Steps)

Excel Reference Named Range in Another Sheet (3 Examples)

Leave a reply Cancel reply