With the CONCATENATE function, you can join two or more texts altogether. Unfortunately, there is no way to bold text in the 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 a 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.
Bold Text in Concatenate Formula by Using VBA in Excel: 2 Methods
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 the 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.
Similarly, you can get bold text in a concatenate formula for all other rows.
Read More: How to Concatenate with Delimiter in Excel
2. Bold Text in Concatenate Formula for a Range
You can also create a Macro to bold text in a concatenate formula for a range of data.
Read More: How to Concatenate with Space in Excel
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 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.
Read More: How to Concatenate Apostrophe in Excel
2.2. For Entire Text
Let’s say this time you want to bold the entire text in the concatenate formula. To do 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 OK.
As a result, you will get the entire text bold in the concatenate formula.
Read More: How to Concatenate Cells but Keep Text Formatting in Excel
Download Practice Workbook
Conclusion
I hope now you know how to bold text in a 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 a concatenate formula for a range. If you have any confusion about any of these two methods, please feel free to leave a comment.
Related Articles
- CONCATENATE vs CONCAT in Excel
- Excel CONCATENATE Showing Formula Not Result
- Concatenate Not Working in Excel
- Opposite of Concatenate in Excel
<< Go Back to Excel Concatenate Text |Â Concatenate Excel | Learn Excel
I need this for Google Sheets. please mail me for update
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:
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