How to combine cells and keep the cell formatting in Excel?

Sometimes, you may want to merge multiple cells in Excel into one cell, while also preserving the formatting of the original data, such as number formats (for instance, percentages, currency), text colors, font styles, and more. A common scenario might be when one cell contains a name and another holds a value formatted as a percentage – after combining, you want the resulting cell to display both the value and its original formatting. This can be challenging because basic combining operations in Excel often result in loss of source cell formats. In this article, you will find several practical methods for combining cells and retaining their formatting, along with an explanation of when each approach may be best suited to your needs.

Combine cells and keep the cell formatting with formula
Combine cells and keep the cell formatting with Microsoft Word
Easily combine cells and keep the cell formatting with Kutools for Excel
Combine cells and preserve formats using VBA code
Use Flash Fill to combine cell contents (with format limitations)

Combine cells and keep the cell formatting with formula

As shown in the screenshot below, let’s say cell A1 contains the name "Nana" and cell B1 contains "75.62%" with percentage formatting. If you want to combine A1 and B1 so the result appears as "Nana 75.62%" in cell C1, you can use a formula to not only concatenate the contents but also preserve the number formatting (in this case, percentage) for display. This method is best when you only need to preserve display formats like number, date, or time formatting, but cannot maintain font color or cell fill color.

Click to select cell C1, then enter the following formula in the Formula Bar:

=A1 & " " & TEXT(B1,"0.00%")

After entering the formula, press Enter. The two cells are merged, with the percentage format retained in the combined result.

Note: In this formula, B1 is the cell with the percentage format. Adjust the cell references and number format string as needed (for example, use "mm/dd/yyyy" for dates). Keep in mind that this approach is effective for number formatting shown as text, but it does not preserve cell-level formatting such as text color, bold, or background fill.

If you need to apply this combination to multiple rows, simply drag the fill handle from C1 down to copy the formula to other rows. Always ensure your format string in the TEXT function matches the type of data you want to display.

Advantage: Quick for small datasets and when only number formatting needs preservation.
Limitation: Cannot keep special cell visual formats or styles such as font color or background color.

Use Flash Fill to combine cell contents (with format limitations)

Flash Fill, a built-in feature in Excel 2013 and later, can automatically combine cell contents based on a pattern you specify. This tool is user-friendly for textual data manipulation, especially for quick merges where retyping is not practical. However, Flash Fill focuses on content and does not usually retain cell formatting such as font color or number format in the merged cell—it simply follows the visible value, not its underlying format.

How to use Flash Fill for combining cell values:

1. Suppose cells A2 and B2 have "Nana" and "75.62%" (as text or formatted value), and you want C2 to display "Nana 75.62%". In cell C2, type your desired result.
2. Move to cell C3. Start typing the next expected merge result or simply press Ctrl+E to let Excel suggest and fill in the rest of the column based on your example pattern.
3. Flash Fill will automatically complete the merged pattern for the remaining rows, saving you time on retyping or copying formulas.

Tips:

• If Flash Fill does not start automatically, you can manually trigger it with Data > Flash Fill in the Data Tools group, or by pressing Ctrl+E.
• This technique works well for combining first and last names, joining codes and descriptors, etc., but be aware that merged cells produced by Flash Fill standardize on what you type—original formatting such as cell color, bold, or data type formatting (like percentages) is not preserved, only the plain text result.
• Flash Fill is particularly helpful for pattern-based merges over small-to-medium datasets and as a supplement where complex formats are not needed.

Advantage: Extremely quick, requires no formulas or code.
Limitation: Does not keep formatting—only merges cell values as text.

Combine cells and keep the cell formatting with Kutools for Excel

The method involving Word may require switching between applications frequently and works best for small collections of data. For more complex merging tasks that require automation, consistency, and efficient handling of various formats (including number/date formats and cell styles), you can use the Kutools for Excel add-in. The Combine feature helps you combine cells while keeping their original formatting intact, whether that's date, percentage, color, or font style.

1. Select the cells you want to combine, and click Kutools > Merge & Split > Combine Rows, Columns or Cells without Losing Data.

Note: If you want to place the result in a new column or row, you need to select one more column beside your original data. For example, select an extra column if you're combining horizontally.

2. In the Combine Columns or Rows dialog box:

Notes:

3. The selected cells will now be combined with formatting preserved, as demonstrated below:

Using the Combine utility in Kutools for Excel allows you to merge and retain percentage, date, and other formatting styles with minimal effort. It is ideal for frequent, batch, or multi-type format operations and supports large datasets.

Combine cells and keep the cell formatting with Microsoft Word

If your worksheet includes different types of formatting—such as bold, italic, text color, or more advanced formats—the previous formula method will not fully preserve these styles. In such cases, you can use Microsoft Word’s table handling features to help. This method is particularly helpful if you need to merge cells from two columns while retaining rich text formatting, which is often not possible directly in Excel.

For example, as shown below, column A contains text and column B contains values with specific formatting:

To recombine the two columns and keep the formatting, follow these steps:

1. Select and copy the two columns of data from Excel.
2. Open a new Word document and paste the data. The content is usually pasted as a table for easier manipulation.
3. Select the newly created table. Click the Layout tab under Table Tools, then use Convert to Text in the Data group as shown below:

4. In the Convert Table to Text dialog box, choose your preferred separator (such as a space, comma, or semicolon). Do not select "Paragraph marks" or "Tabs" for best results:

5. Click OK to convert the table. Next, copy the combined text results from Word and paste them back into the Excel worksheet in your desired location. The output will display merged content, with text formatting preserved to the extent possible through the copy-paste process:

Note: Formatting such as bold, italic, and font color is generally maintained, but complex conditional formatting or formulas will not transfer through this process. Also, ensure you carefully handle the separator choice to prevent data confusion.

Advantage: Useful for preserving font styles and most text formatting.
Limitation: Not suited to automate large ranges or keep cell background colors and Excel-specific formats. Paste results may require manual adjustment.

Combine cells and preserve formats using VBA code

When working with data that has custom cell formatting—such as font color, bold/italic, cell shading, and number formatting—basic formulas or built-in combining features in Excel often result in loss of formatting. Using a VBA macro offers a more flexible and automated way to merge cell contents and retain a wide range of styles, including text attributes and most number formats. This approach is well-suited for repetitive combining tasks or when combining large amounts of formatted data programmatically.

Note: Macros require enabling the Developer tab in Excel and saving your file as a macro-enabled workbook for use later. Always back up your data before running VBA code to prevent unexpected results.

1. Go to Developer > Visual Basic. In the VBA editor, click Insert > Module and paste the following code into the module window:

Sub CombineCellsPreserveFormat()
    Dim TargetCell As Range
    Dim Cell As Range
    Dim CombineRange As Range
    Dim xTitleId As String
    Dim OutputText As String
    Dim OutputRtf As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set CombineRange = Application.Selection
    Set CombineRange = Application.InputBox("Select cells to combine:", xTitleId, CombineRange.Address, Type:=8)
    
    Set TargetCell = Application.InputBox("Select target cell:", xTitleId, CombineRange.Cells(1).Address, Type:=8)
    
    OutputText = ""
    OutputRtf = ""
    
    For Each Cell In CombineRange
        Cell.Select
        OutputRtf = OutputRtf & Cell.Text & " "
    Next
    
    TargetCell.Select
    TargetCell.Value = ""
    
    TargetCell.Select
    
    For Each Cell In CombineRange
        Cell.Copy
        TargetCell.Select
        Selection.PasteSpecial Paste:=xlPasteFormats
        TargetCell.Value = TargetCell.Value & Cell.Text & " "
    Next
    
    Application.CutCopyMode = False
    
    MsgBox "Cells combined with formatting. Please review merged cell.", vbInformation, xTitleId
End Sub

2. To run the code, click the button in the VBA editor. A prompt will let you select the cells to combine and a target cell for the merged result. The macro will then concatenate the text and attempt to preserve most cell font formatting in the target cell.

Tips & Considerations:

• Merged cell will have the combined text. Most cell-level formats (such as font color, bold, underline, italics, font size and number format) are preserved, though background fills and conditional formatting may not fully transfer due to Excel's text object model limitations.
• If values use different types of formatting, only the first cell's format may get fully applied, so double-check and manually adjust formatting if necessary.
• If you experience a "run-time error", ensure that you have correctly selected a range and a target cell, and that you have appropriate macro permissions in your Excel settings.
• This VBA can only merge one row of data at a time.

Advantage: Highly customizable and useful for advanced users or batch operations.
Limitation: May not perfectly retain all types of formatting, depending on content complexity and Excel version. Requires basic familiarity with VBA.

In summary, the approach you select for combining cells while preserving formatting in Excel should be guided by the nature of your data and specific formatting requirements. The formula method is very quick for simple number formats, Word provides broader formatting preservation for text, Kutools delivers comprehensive and convenient handling for different data types and large volumes, VBA allows flexible automation with most format retention, and Flash Fill is perfect for text-only merges with minimal setup. If issues arise (such as losing formatting or errors in copying), review your steps for correct range selection, ensure compatibility with your Excel version, and consider data backup before applying automated tools or code.