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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

Similarly, you can get bold text in a concatenate formula for all other rows.

how to bold text in concatenate formula in excel

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.

In this article, I’ll show you how to bold text in concatenate formula in Excel.

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.

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

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

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


<< Go Back to Excel Concatenate TextConcatenate Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Prantick Bala
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

2 Comments
  1. 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.
      Dataset for Bold Text in Concatenation
      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:
      Output of Bold Text in Concatenate
      You can download the Spreadsheet from the link below:
      Bold text in Concatenate

      Let us know your feedback.

      Regards,
      Seemanto Saha
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo