[Solution:] Excel Wrap Text Not Working for Merged Cell

Often you might need to wrap text over merged cells while working in Excel. Especially, if the string inside the cells is quite larger. Unfortunately, you may get into trouble accomplishing the task. Obviously, you should identify the issue and the reason behind happening this. More importantly, you have to fix the issue effectively. In this instructive session, I am going to show you 5 ways to wrap the text for not working over the merged cell in Excel.


Download Practice Workbook


Reasons and Solutions for Excel Wrap Text Not Working Merged Cell

Let’s introduce today’s dataset where some Quotes are given with the corresponding Authors. As you see, each quote covers 3 cells and is unable to show the text completely. So, you need to wrap the text after merging those cells.

Dataset Excel Wrap Text Not Working Merged Cell

Let’s identify the issue and later you’ll see the solution to fix the issue.


Issue Identification

Primarily, you may apply the widely used tool for wrapping text and merging cells combinedly. Then, have a look at the output.

➱ In the first place, select any single quote (e.g. B5:D5 cell range). Next, click on the Merge & Center option, and then the Wrap Text option from the Alignment ribbon in the Home tab.

Issue Identification

➱ Alternatively, you can use the Format Cells option (just press CTRL  + 1 to open the Format Cells dialog box). Thereafter, go to the Alignment tab and check the boxes before the Wrap Text and Merge Cells option.\

Issue Identification

Eventually, you’ll get the following output.

Issue Identification

Note: you cannot merge all the cells containing your dataset with a single click. Rather than you have to pick the adjacent cells to merge.

If you repeat the same process over the below cells, you’ll get the following output.

Issue Identification

Later, apply the ways to autofit the row. For example, click on the AutoFit Row Height from the Format opinion in the Home tab to extend the row automatically.

Issue Identification

After doing that, you’ll get the following output where the Wrap Text option is not working over the merged cells e.g. Row 5, 8, and 9. Exceptionally, you’ll get the accurate output for Row 6 and 7 as the text inside those cell ranges doesn’t exceed.

Excel Wrap Text Not Working Merged Cell Issue Identification

So, what’s the reason for this happening?

Simply, it is a drawback of Excel and Microsoft says you cannot use the AutoFit feature for merged cells.


Solve the Issue of Not Working Wrap Text over Merged Cell


1. Adjust Row Height and Column Width Manually

In the beginning method, you’ll see the simple way to solve the issue i.e. adjusting the row height and column width manually.

Firstly, select any cell and go to the row number. Then, drag down your cursor.

Adjust Row Height and Column Width Manually

Otherwise, you can input row height directly after selecting the Row Height option from the Format feature.

Adjust Row Height and Column Width Manually

Now, input a suitable row height e.g. 32.

Excel Wrap Text Not Working Merged Cell Adjust Row Height and Column Width Manually

After adjusting the row height, you’ll get your desired output where both the Wrap Text and Merge Cells options are working well.

Excel Wrap Text Not Working Merged Cell Adjust Row Height and Column Width Manually

Similarly, you may adjust the column width if you have merged cells in columns.


2. Unmerge Cell Then Wrap Text

If you are able to skip the merging cell issue, try to avoid that. As it will ease the complexity.

So, you need to pick the Unmerge Cells option from the Merge & Center option while keeping your cursor over any quote.

Unmerge Cell Then Wrap Text

Subsequently, you’ll get the following unmerged cells.

Unmerge Cell Then Wrap Text

Next, select all the cell ranges and click on the Wrap Text option.

Unmerge Cell Then Wrap Text

Then, choose the AutoFit Row Height option.

Unmerge Cell Then Wrap Text

Shortly, you’ll get the expected output.

Unmerge Cell Then Wrap Text


3. Change the Alignment Before Merging Cells

In addition, you may change the Alignment to keep working the wrap text over the merged cells.

So, select the unmerged cells, and choose the Format Cells option from the Context Menu.

Excel Wrap Text Not Working Merged Cell Change the Alignment Before Merging Cells

Next, go to the Alignment tab and specify the Horizontal alignment as Center Across Selection. Besides, check the box before the Wrap text option.

Excel Wrap Text Not Working Merged Cell Change the Alignment Before Merging Cells

Right after doing that use the ways to autofit and you’ll get the wanted output.

Excel Wrap Text Not Working Merged Cell Change the Alignment Before Merging Cells


4. Insert Line Break to Solve Wrap Text Not Working Merged Cell

Furthermore, if you want to merge the adjacent field e.g. two separate fields i.e. Quotes and Authors into a single field i.e. Quotes and Corresponding Authors, you’ll do that easily. Conversely, you might have the following dataset where the Quotes are combined along with the Authors including the hyphen separator.

Whatever, go to the before the hyphen, and press ALT + ENTER to insert a line break.

Excel Wrap Text Not Working Merged Cell Insert Line Break

Consequently, you’ll get the following output containing line breaks.

Insert Line Break

Afterward, you’ll get the desired output if you use the Autofit option.

Excel Wrap Text Not Working Merged Cell Insert Line Break


5. Using the VBA Code to Solve Wrap Text Not Working Merged Cell

What if you have to insert a code and you’ll get the wanted output without using any other option in Excel?

Yes. Definitely, is possible. Before doing that have a closer look at the dataset again.

Using the VBA Code

So, you have to create a module in Macro.

Firstly, open a module by clicking Developer > Visual Basic.

How to Insert VBA Code

Secondly, go to Insert > Module.

How to Insert VBA Code

Then, copy the following code into the newly created module.

Sub MergeWrap_VBA()
Dim rn As Long, Mrow As Long, ERow As Long, MCol As Long
Dim cn As Long, n As Long, rh As Single, mr As Long
Application.DisplayAlerts = False
Sheets("Using VBA").Copy After:=Sheets(Sheets.Count)
On Error Resume Next
ActiveSheet.Name = "Outupt Using VBA"
Cells().WrapText = True
Rows.AutoFit
MCol = Cells(1, 1).End(xlToRight).Column
Mrow = Cells(Rows.Count, 1).End(xlUp).Row
For rn = Mrow To 2 Step -1
    If Cells(rn, 1).Value = "" Then
        If ERow = 0 Then ERow = rn
       ElseIf ERow > 0 Then
        rh = Rows(r).RowHeight
        n = ERow - rn + 1
        If n > 0 Then Rows(rn & ":" & ERow).RowHeight = h / n
        For cn = 1 To MCol
               For mr = ERow To rn Step -1
                If Cells(mr, cn).Value = "" Then
                       Range(Cells(mr, cn), Cells(mr - 1, cn)).MergeCells = True
                   End If
            Next
        Next
        ERow = 0
    End If
Next
Application.DisplayAlerts = True
With Worksheets("Outupt Using VBA").Columns("B")
 .ColumnWidth = .ColumnWidth * 2
End With
End Sub

Excel Wrap Text Not Working Merged Cell Using the VBA Code

In the above code, I declared the variables first. Then, I used the code to generate a copy of the input into a new sheet. Later, I assigned the WrapText to True to wrap the texts. Subsequently, I utilized the Range.End property to return the maximum column and row number respectively. More importantly, I ran the For loop from bottom to top to get the desired output. Lastly, I applied the Column.Width property to increase the width of column B.

Things to Change While Using the Code:

  • Firstly, you have to input the active sheet name (e.g. “Using VBA”) where the dataset is available.
  • Secondly, you have to change the output sheet as it is “Output Using VBA” in my case.
  • Moreover, you need to define the column where you want to get the wrap text over the merged cell.

Next, if you run the code (the keyboard shortcut is F5 or Fn + F5), you’ll see the following output.

Excel Wrap Text Not Working Merged Cell Using the VBA Code


Conclusion

That’s the end of today’s session. I strongly believe that from now you may fix the issue of keeping the wrap text not working over the merged cell. Anyway, if you have any queries or recommendations, please share them in the comments section below.

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo