2 Ways to Bold Text in Concatenate Formula in Excel

When working with large amounts of data in Excel, it’s important to ensure the information is clear and easy to read. One way to do this is by using text formatting, such as bolding specific text. This can be especially useful when concatenating text in formulas, as it can help highlight important information. In this article, we will explore two methods that will help us to bold certain text in concatenate formula in Excel.

Contents hide

📁 Download Excel File

Learn to Bold Text in Concatenate Formula in Excel with These 2 Methods

1. Bold Part of Text

2. Bold Text For a Range

2.1. For Part of Text

2.2. For Entire Text

How to Bold Specific Text in Excel Cell

📝 Takeaways from This Article

Conclusion

📁 Download Excel File

Download this file to understand better and practice.

Bold Text in Formula.xlsx

Learn to Bold Text in Concatenate Formula in Excel with These 2 Methods

You can bold entire text in a cell using Excel’s built-in function. But when it comes to a particular text, there is no built-in feature. In this article, we will be discussing how to use Visual Basic for Applications (VBA) to concatenate the contents of two cells and print the result in a third cell, while also formatting a specific portion of the concatenated text in bold.

VBA is a programming language that is built into Microsoft Office applications such as Excel, and it allows users to automate tasks and create custom functions. In this case, we will be using VBA to write simple codes that will concatenate the contents of two cells and then format a specific portion or the whole content of the concatenated text as bold. This can be a useful technique for anyone who needs to combine data from different cells and make certain parts of the resulting text stand out.

Method 1

1. Bold Part of Text

Our first code will bold a specific text in the concatenate formula-induced result. In our example, the company name is in column B, and the net worth is in column C. We want to concatenate the contents of these two columns and print them in Column D. The interesting thing we want to do is bold the content of Column C in the concatenated result. So, your first instinct might be to Bold the content in Column C first, then use the CONCATENATE function to join them up. But the CONCATENATE or CONCAT functions don’t retain the formatting of the cell in their output. That is why we will use VBA coding to bold the text we want.

⬇️⬇️ STEPS ⬇️⬇️

Firstly, pressing Alt+F11 will open Microsoft Visual Basic, where we insert a Module from the Insert tab to open a whiteboard.

Now copy and paste the following code into the module.

Sub Boldtext()
Dim CP As String, NW As String
CP = Range("B6")
NW = Range("C6")
Range("D6").Value = CP & "," & NW & "$"
Range("D6").Font.Bold = False
Range("D6").Characters(Len(CP) + 2, Len(NW) + 1).Font.Bold = True
End Sub

Finally, press F5 to execute the code and bold the text in cell C6 in the concatenated output.

Just change the Cell index number and run the code for all the other rows.

📕 Read More: 5 Easy Ways to Add Comma in Excel to Concatenate Cells

Method 2

2. Bold Text For a Range

The previous method was for a single cell. So how would you change the code for a range of cells? Fear not, we have a code for you when you want to bold particular text in Concatenate formula result in Excel. Using the code you can either bold specific text or the entire text. So let’s write our code, then.

Range

2.1. For Part of Text

For this section, we will write a code that will bold the content of Column C in the concatenated text in Column D, and it will be used for a range of cells, meaning one code, and that’s it.

⬇️⬇️ STEPS ⬇️⬇️

Firstly, pressing Alt+F11 will open Microsoft Visual Basic, where we insert a Module from the Insert tab to open a whiteboard.

Now copy and paste the following code into the module.

Sub ConcatenateAndBold()
    Dim LR As Long
    LR = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
    For i = 6 To LR
        Cells(i, 4).Value = Cells(i, 2).Value & "," & Cells(i, 3).Value & "$"
        Cells(i, 4).Characters(Len(Cells(i, 2).Value) + 2, Len(Cells(i, 3).Value) + 1).Font.Bold = True
    Next i
End Sub

Finally, press F5 to execute the code and bold the text in cell C in the concatenated output.

📕 Read More: 11 Easy Ways to Concatenate Rows in Excel

Range

2.2. For Entire Text

For this section, we will write a code that will bold the concatenated text in Column D. And it will be used for a range of cells, meaning one code and that’s it.

⬇️⬇️ STEPS ⬇️⬇️

Firstly, pressing Alt+F11 will open Microsoft Visual Basic where we insert a Module from the Insert tab to open a whiteboard.

Now copy and paste the following code into the module.

Sub ConcatenateAndBold()
    Dim LR As Long
    LR = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
    For i = 6 To LR
        Cells(i, 4).Value = Cells(i, 2).Value & "," & Cells(i, 3).Value & "$"
        Cells(i, 4).Font.Bold = True
    Next i
End Sub

Finally, press F5 to execute the code and bold the entire text in the concatenated output.

📕 Read More: 5 Easy Methods to Combine Rows into One Cell in Excel

How to Bold Specific Text in Excel Cell

One way to make your data more easily readable is by using formatting options such as bolding specific text. Now, we will show you how to bold common, specific text within a range of cells in Excel. This can be useful for highlighting important information, such as keywords or specific values, and making your data more easily readable. This method can be applied to both individual cells or entire ranges of cells, making it a versatile solution for formatting your data.

We have a collection of Harry Potter books and in the names, we want to highlight just “Harry Potter”.So let’s write a code in VBA script for that. Please keep in mind that there is no feature in Excel for formatting specific text within a string of text.

⬇️⬇️ STEPS ⬇️⬇️

Firstly, pressing Alt+F11 will open Microsoft Visual Basic, where we insert a Module from the Insert tab to open a whiteboard.

Now copy and paste the following code into the module.

Sub BoldSpecificText()
Dim textToBold As String
textToBold = InputBox("Enter the text:")
For Each cell In Selection
    If cell.Column = 2 Then
        If InStr(1, cell.Value, textToBold, vbTextCompare) > 0 Then
            cell.Characters(InStr(1, cell.Value, textToBold, vbTextCompare), Len(textToBold)).Font.Bold = True
        End If
    End If
Next cell
End Sub

Now go back to your active workbook and select your range. We selected B6:B13.

Finally, in the module, press F5 to execute the code, and a dialogue box will appear to ask for the particular text. We will “Harry Potter” here.

After you press OK, you will see in every row, “Harry Potter” has turned bold.

📝 Takeaways from This Article

🖊️ Firstly, we created a code for a single cell where particular text will be bold in the concatenate formula in Excel.

🖊️ Then, we created another code that will be used for a range to bold particular text or the entire text in concatenate formula in Excel.

Conclusion

In conclusion, formatting text in Excel can make a significant difference in how easily your data can be understood. Bolding text in a formula-induced concatenated text can be a great way to draw attention to important information. I hope that after reading this article, you can easily bold text in concatenate formula in Excel. If you have any questions, feel free to comment below. I will try to answer them as soon as possible. Have fun using Excel, and you can visit Excelden.com for more tutorials. Thank You.