Md. Abdul Kader
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.
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.
➱ 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.\
Eventually, you’ll get the following output.
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.
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.
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.
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.
Otherwise, you can input row height directly after selecting the Row Height option from the Format feature.
Now, input a suitable row height e.g. 32.
After adjusting the row height, you’ll get your desired output where both the Wrap Text and Merge Cells options are working well.
Similarly, you may adjust the column width if you have merged cells in columns.
Read More: Excel Auto Fit Row Height for Wrap Text (4 Methods)
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.
Subsequently, you’ll get the following unmerged cells.
Next, select all the cell ranges and click on the Wrap Text option.
Then, choose the AutoFit Row Height option.
Shortly, you’ll get the expected output.
Read More: How to Wrap Text in Merged Cells in Excel (5 Ways)
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.
Next, go to the Alignment tab and specify the Horizontal alignment as Center Across Selection. Besides, check the box before the Wrap text option.
Right after doing that use the ways to autofit and you’ll get the wanted output.
Read More: How to Wrap Text across Multiple Cells without Merging in Excel
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.
Consequently, you’ll get the following output containing line breaks.
Afterward, you’ll get the desired output if you use the Autofit option.
Read More: [Solution:] Excel Wrap Text Not Working for Merged Cell
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.
So, you have to create a module in Macro.
Firstly, open a module by clicking Developer > Visual Basic.
Secondly, go to Insert > Module.
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
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.
Read More: Excel VBA: Autofit Row Height with Wrap Text (5 Effective Ways)
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.
