How to bold part text when concatenate two columns in Excel?
Working with data in Excel, you often need to merge information from two columns into a single cell for better readability or reporting. For example, you may want to show customer names along with their locations or combine product names and codes. Normally, you achieve this by using formulas like CONCATENATE or &. However, a common issue arises: after concatenating cell values, Excel treats the combined text as a simple string and does not support partial formatting natively. That means you cannot automatically bold just a part of the combined text via formulas—any formatting applied will affect the whole cell only. This limitation can be frustrating when you want to emphasize a specific segment, such as making the first word bold. In this tutorial, we’ll show you how to achieve partial bold formatting when joining two columns in Excel and discuss practical scenarios for this, as well as alternative solutions.
Bold part text when concatenating two columns with VBA code
Alternative: Concatenate values using formulas (with formatting tips)
Bold part text when concatenating two columns with VBA code
Consider a situation where you have two columns: one with first names and another with last names. You want to combine them into a new column, but you want the first name (from the first column) in bold and the last name in regular style, similar to professional display formats. The screenshot below illustrates this goal, where the combined result stands out with a bolded first segment:
This approach is especially useful for lists where you want to highlight a primary piece of information (such as a name or product code) within a larger description. While regular Excel formulas cannot apply partial cell formatting, you can use a VBA macro to automate the merging and formatting process. This is suitable for users comfortable with simple macros and who want a repeatable solution for partial formatting in concatenated results. Bear in mind that running VBA requires enabling macros and cannot be undone with Ctrl+Z, so make sure to save your work before use.
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window. If this is your first time using VBA, make sure you have the Developer tab enabled in Excel (File > Options > Customize Ribbon > check "Developer").
2. In the VBA window, click Insert > Module, which will create a new module. Then paste the following code into the code area of the module. This macro will merge two column values in each row and make the first value bold in the output:
VBA code: Bold part text when concatenating two columns:
Sub JoinCellsWithBoldFormatForFirstWord()
'Updateby Extendoffice 20160725
Dim xRg As Range
Dim xTxt As String
Dim xCell As Range
Dim I As Long
On Error Resume Next
If ActiveWindow.RangeSelection.Count > 1 Then
xTxt = ActiveWindow.RangeSelection.AddressLocal
Else
xTxt = ActiveSheet.UsedRange.AddressLocal
End If
LInput:
Set xRg = Application.InputBox("Please select the data range:", "Kutools for Excel", xTxt, , , , , 8)
If xRg Is Nothing Then Exit Sub
If xRg.Areas.Count > 1 Then
MsgBox "does not support multiple selections", vbInformation, "Kutools for Excel"
GoTo LInput
End If
If xRg.Columns.Count <> 2 Then
MsgBox "only two columns in the selection", vbInformation, "Kutools for Excel"
GoTo LInput
End If
Set xRg = xRg.Resize(xRg.Rows, 3)
On Error Resume Next
For Each xCell In xRg.Columns(3).Cells
xCell = xRg.Cells(xCell.Row, 1) & " " & xRg.Cells(xCell.Row, 2)
xCell.Font.Bold = False
xCell.Characters(1, Len(xRg.Cells(xCell.Row, 1))).Font.FontStyle = "Bold"
Next
End Sub
3. On the menu, press F5 to execute the macro. A prompt window will appear, allowing you to select the range of cells you want to concatenate and format. You can use your mouse to select, or type in the range, such as A1:B10. See below for the selection screen:
Tips before running:
- Make sure the selected range covers both columns you wish to merge.
- This macro will output results beside the original selection, so verify the destination cells are empty to avoid overwriting your data.
- If you have any merged cells or formulas within the selected range, separate or clear them, as this may cause errors.
If you accidentally select the wrong range, click Cancel and rerun the macro.
4. Click OK in the prompt. The macro will process the data, and for each row in your selected range, it will combine the values from both columns into a new cell on the right. The first value in each result will be formatted in bold automatically, as shown below:
You can now use these results in further reporting, printing, or exporting. If the results are not as expected, double-check your selection and ensure each row has the expected values in both original columns.
Pros:
- Enables partial formatting (bold, or other style changes) during concatenation—something standard formulas cannot do.
- Automates repetitive tasks for large lists.
Cons:Requires running macros, which may be restricted in some environments.
If you encounter issues (such as no output, errors, or formatting not applied):
- Check the security settings to make sure macros are enabled (File > Options > Trust Center > Trust Center Settings > Macro Settings).
- Ensure there are no protected sheets or merged cells in the target area.
- If the range is incorrectly selected, rerun the macro and select two adjacent columns only.
Alternative: Concatenate values using formulas (with formatting tips)
For users who do not wish to use VBA, Excel formulas like CONCATENATE, TEXTJOIN, or simply the ampersand (&) can merge values from two columns. Although formulas cannot apply bold formatting to part of the output, you can quickly join columns and then manually format the text in each cell as needed.
To concatenate two columns (A and B) in Excel:
1. Click on the first cell in the destination column (for example, C2), then input this formula:
=A2 & " " & B22. Press Enter to confirm and get the combined result. Drag the fill handle down to apply the formula to other rows as needed. If you need to manually bold part of the text in the formula result, first copy and paste the result cells as values in the same location. Then double-click the cell, select the desired part, and press Ctrl + B or use the Home > Bold button.
3. If you want to use other separators or custom formats, adjust the formula as required. For instance, to add a comma between the texts:
=A2 & ", " & B2Pros:
- Simple and requires no macros or special settings.
- Flexible to combine any number of cells or include custom separators.
Cons:
- Partial formatting (bolding only part of text) must be applied manually and is not dynamic—updates to values require reformatting.
- Does not scale well for very large datasets if manual formatting is required.
For best results when partial formatting is needed, use for small datasets or presentation-ready tables after finalizing the data.
In summary, using VBA makes partial formatting of concatenated results feasible for large or automated projects, while formulas are best for basic merging and are easy for everyday use. If you want formula-only solutions with no formatting concerns, you can refer to standard Excel concatenation techniques. Always back up your workbook before using macros and double-check your selections before running any automated task.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in