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

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.


Download Practice Workbook


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.

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.

CODE

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.

bold text in Excel concatenate

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.

bold text in Excel concatenate

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

CODE

After changing the cell reference,

➤ Click on the Run icon or press F5.

RUN

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.

how to bold text in concatenate formula in excel

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

how to bold text in concatenate formula in excel

Read More: Combine Text in Excel (8 Suitable Ways)


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.

In this article, I’ll show you how to bold text in concatenate formula 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.

how to bold text in concatenate formula in excel

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.

bold text in Excel concatenate

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.

bold text in Excel concatenate

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.

input box

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.

bold text in Excel concatenate


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

code

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.

macro

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.

bold text in Excel concatenate

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

result

Read More: How to Concatenate Range in Excel (For Both Old and New Versions)


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.


Related Articles

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo