How to Concatenate Different Fonts in Excel (2 Easy Ways)

Many Excel users need to combine text from numerous cells into one cell where they use the Excel CONCATENATE function. However, you may want to concatenate text that has different fonts like Calibri, Times New Roman, and Amasis MT Pro Light as well as font styles, such as bold or italic. This can not be possible as the CONCATENATE function only outputs text in a single font. In such cases, you can use VBA codes to achieve the desired result. This article will explain how to concatenate text with different font styles in Microsoft Excel.

How to Concatenate Different Fonts in Excel: 2 Easy Ways

In this blog post, we will explore how to use VBA to concatenate text in Excel while preserving different font styles. Concatenating text is a common task in Excel, but when it comes to preserving different font styles, it can be trickier. With the use of VBA, however, we can create a macro that will take the text with different fonts from two cells, and concatenate them into a third cell without losing the font styles. You can do this in 2 ways. Let’s have a quick look at the processes.

1. Use Cells.Value Property in Excel VBA to Join Different Fonts

Cells.Value gives access to a particular cell in the Excel VBA. You can also extract data from a cell by using this property. Here the value of a single cell in the current worksheet is referred to as Cells.Value.

To launch Excel’s Visual Basic Editor, go to the Developer tab >> select Visual Basic. You can also press Alt + F11. This shortcut will open up Microsoft Visual Basic for the Application.

In Microsoft Visual Basic for Applications window, select Module under Insert in the editor which creates Module1 under Modules.

You now can write your code in the new module or copy the code below and paste it into your module. After that, press Run.

Sub mergeFONT()
For k = 5 To 15
Cells(k, 4).Value = Cells(k, 2).Value & " " & Cells(k, 3).Value
For i = 1 To Cells(k, 2).Characters.Count
Cells(k, 4).Characters(i, 1).Font.Name = Cells(k, 2).Characters(i, 1).Font.Name
Next
For i = 1 To Cells(k, 3).Characters.Count
Cells(k, 4).Characters(Cells(k, 2).Characters.Count + i + 1, 1).Font.Name = Cells(k, 3).Characters(i, 1).Font.Name
Next
Next k
End Sub

For instance, Cells(5, 4).Value means the value of the cells in the 5th row and the 4th column. As Cells(k, 4).Value is in a loop where k varies from 5 to 15 which indicates all cells in the Full Name column. This VBA code not only concatenates cells but also restores their original font styles.

The text from cells B5 and C5 will be combined into cell D5 while retaining their separate fonts. All the fonts of cells in different rows of the table will be combined after running the VBA code.

2. Concatenate Different Fonts with Range.Value Property in VBA

In this code, we will use Range.Value property instead of Cells.Value. Range.Value indicates all the values in a predefined range. The key difference here to note is that Range.Value can return the two-dimensional array.

Similar to the previous stage, to launch Excel’s Visual Basic Editor, go to the Developer tab >> select Visual Basic or press Alt + F11. This action will open up Microsoft Visual Basic for Applications.

Select Module under Insert in the editor in Microsoft Visual Basic for Applications window. You may then write your macro in the newly created module that is created.

Now copy the following code in your newly created module. After entering the code press Run.

Sub mergeRANGEFONT()
For j = 5 To 15
Range("D" & j).Value = Range("B" & j).Value & " " & Range("C" & j).Value
For i = 1 To Range("B" & j).Characters.Count
Range("D" & j).Characters(i, 1).Font.Name = Range("B" & j).Characters(i, 1).Font.Name
Next
For i = 1 To Range("C" & j).Characters.Count
Range("D" & j).Characters(Range("B" & j).Characters.Count + i + 1, 1).Font.Name = Range("C" & j).Characters(i, 1).Font.Name
Next
Next
End Sub

Finally, the text from cells B5 and C5 will be combined into cell D5 with separate font styles. This VBA code will combine all the fonts of cells in different rows of the table.

How to Use Two Fonts in One Cell in Excel Without VBA

Excel users can change the font style of words in a single cell without any formula or VBA code. This kind of procedure is helpful when you have a few words to change. In the following section, we demonstrate how we can do it so easily by just selecting and changing the font styles.

Suppose, we have two words in a single cell. We can edit the font of different words separately. Select the word “Concatenate” in cell B4, and select the font as Amasis MT Pro Light font with bold and italic styles.

We have turned a word into a preferred style. Moreover, we can repeat the whole process for other words in a cell as long as required.

How to Concatenate Cells with Different Formats in Excel

Cells with General format and Accounting format can be merged by using the CONCATENATE function but you’ll lose the original formatting. In the following example, we have the Product column in the General format, the Price column in the Accounting format, and the Concatenation column in the General format. Here, we have also used the TEXT function.

You have to insert the following formula in D5 to join cell B5 and cell C5.

=CONCATENATE(B5, " Price: ",TEXT(C5,"$#,##0.00_);($#,##0.00)"))

Formula Explanation:

TEXT(C5,”$#,##0.00_);($#,##0.00)”): C5 is changed into a text string and given a particular number style. “$#,##0.00_);($#,##0.00)” indicates that positive numbers will be shown with a dollar sign, comma separators, and two decimal places.
Between the value in cell B5 and the structured value in cell C5, the “Price:” text string will be inserted.

At last, the CONCATENATE function will merge B5, “Price:” and structured value in cell C5.

Output: SSD Price: $100

How to Concatenate Text and Numbers Keeping Formatting in Excel

We’ll go over how to combine text and numbers in Excel while preserving the formatting of the columns, such as the decimal points. In Excel, concatenating text and numbers is a common job, but if you want to keep the style of the columns, particularly if they have different styles, it can be a little challenging.

The formula in D5 is:

=B5 & " " & TEXT(C5, "$#,#0.00")

Formula Explanation:

TEXT(C5, “$#,#0.00”) formats the numerical value in cell C5. In Excel, you can apply the custom number format “$#,#0.00”. For clarity, this format shows numbers with a dollar sign ($) and a thousand separator (,) in the currency format. Additionally, the format shows cents with two decimal points.
The combined string produced by the ampersand operator combines the text in cell B5 with the formatted number in cell C5 while preserving the style of the number.

Frequently Asked Questions

Can I concatenate text with different fonts in Excel?
Answer: Yes, you can concatenate text with different fonts in Excel using VBA code.

Why would I need to concatenate text with different fonts in Excel?
Answer: You may need to concatenate text with different fonts in Excel to create a unique and visually appealing document or spreadsheet.

Are there any limitations to concatenating text with different fonts in Excel?
Answer: Yes, there are some limitations while using the CONCATENATE function to combine text with different fonts in Excel.

How can I ensure the concatenated text with different fonts looks visually appealing?
Answer: To ensure that the concatenated text with different fonts looks visually appealing, you should choose fonts that complement each other and use formatting options such as bold, italic, and underline as needed.

Can I concatenate text with different fonts in Excel for use in other programs?
Answer: Yes, you can concatenate text with different fonts in Excel and then copy and paste it into other programs such as Word, PowerPoint, or Photoshop.

Download Practice Workbook

You can download the practice workbook from the following download button.

Concatenate Texts with Different Fonts in Excel.xlsm

Conclusion

In conclusion, the ability to concatenate text with different font styles in Excel is a useful trick for creating customized and visually appealing data. By using VBA, you can easily combine text from multiple cells while maintaining their font styles. Whether you need to merge bold or italic text, this method will allow you to create dynamic and professional-looking data easily.

Get FREE Advanced Excel Exercises with Solutions!