Rubayed Razib Suprov
While using Excel, we often observe that our text in the cell is so large that they don’t fit properly within the cell. So, what happens, is that those cell texts are going to be overlapped with the neighboring Cells. This is inconvenient as other cell values views are compromised and it became difficult to understand in which cell actually the text is. In this article, we are going to discuss how we can wrap large text in cells automatically with the help of Excel in 5 different ways.
Download Practice Workbook
Download this practice workbook.
5 Ways to Make Text Automatically Wrap Around in Excel
In this article, I am going to use this dataset for demonstrating purposes. In row 4, a long text in Cell D4 is shown. This text is unwrapped and so long that it overlaps other neighboring Cells.
Below there is a Cell containing the same text but in wrapped format. How we get to this wrapped format will discuss here with explanations and demonstrations.
1. Wrap Text in Excel by Format Cells Option
Just clicking the right mouse button or pressing Alt+1, You can easily launch the format tab and convert unwrap text into wrap format text.
Steps
- Select the cell that contains text which needs to be wrapped.
- Then right-click. From the context menu, select Format Cells.
- Next, in the Text control option in the Alignment, check the box of Wrap Text. Then click OK.
- After pressing OK, you will notice that the Text in Cell D6 is wrapped.
Adjust Row height properly to see all the text.
Read More: How Do You Wrap The Text In a Cell (5 Easy Ways)
2. Wrap Text in Excel Using Ribbon
The easiest method to wrap text in Excel is to use Ribbon.
Steps
- At first, select the cell or cell ranges that contain text which needs to be wrapped.
- After that, from the Home tab, click the Wrap Text option from the Alignment group.
- After clicking OK, you will see that all the text in your selected Cell D4 is now in wrap format.
Adjust Row height properly to see all the text.
Read More: Excel Auto Fit Row Height for Wrap Text (4 Methods)
3. Wrap Text Using the Shortcut Command
Using a simple shortcut, you can easily turn unwrapped text into wrapped text.
Steps
- As we can see, the text in Cell D4 is in unwrap format and too long to fit into one Cell.
- To resolve this problem, select Cell D4, then press Alt > H > W. Press it one by one. You don’t have to press it all together.
- After entering this shortcut, you will see text in Cell D4 is now in wrapped condition.
Adjust Row height properly to see all the text.
Read More: Wrap Text in Excel Shortcut Key (2 Hotkeys)
4. Manual Line Breaks to Wrap Text in Excel
You can manually enter the break line to break an unwrapped text into wrapped text.
Steps
- Select the cell that contains text that needs to be wrapped. In this case, it is Cell D6.
- After selecting the cells, double-click the cell to enable edit mode or press F2.
- In the Cell text, place the cursor where you want to break the text.
- Then press Alt+Enter. Which will break the line into two parts.
- Proceed to enter more line breaks if necessary. By repeating this process, you can successfully break the line and make unwrapped text wrapped.
Read More: [Fixed] Wrap Text Not Showing All Text in Excel (4 Solutions)
5. Using VBA to Wrap Text Automatically
Using a simple VBA Macro can drastically reduce the time to wrap the unwrapped text.
Steps
- First, go to the Developer tab, then click Visual Basic.
- Then click Insert, then click Module.
- In the module window, enter the following code:
Sub wrap_text()
Range("D4").WrapText = True
End SubNote :
In this code, Cell D4 indicated that this code is going to execute content in Cell D4, so if you have your data in other Cell, replace Cell D4 with your desired Cell address.
- Then close the window.
- After that, go to the View tab > Macros > View Macros.
- Next, click View Macros, and select the macros that you created just now. The name here is wrap_text. Select and then click Run.
- After clicking Run, you will see all the Text in Cell D4 now wrapped.
In case, someone wants to wrap a range of Cells, like the dataset shown below,
They need to enter the following code in the VBA editor:
Sub wrap_text2()
Range("D4:D7").WrapText = True
End SubNote:
In this code, D4:D7 indicates the range of data that we are choosing. If you want to choose a different range of data, simply replace D4:D7 with your target Cell range address.
- Then close the VBA editor window.
- After that, go to View tab > Macros > View Macros.
- After clicking View Macros, select the macros that you created just now. The name here is wrap_text2. Select it then click Run.
- After clicking Run, you will see that all of your unwrapped text in the range of Cells is in wrap format.
To wrap a whole worksheet, you need to use the following code:
Sub wrap_text3()
ActiveSheet.UsedRange.WrapText = True
End Sub
- Then close the window.
- After that, go to the View tab > Macros > View Macros.
- After clicking View Macros, select the macros that you created just now. The name here is wrap_text3. Select and then click Run.
- After clicking Run, you will see that all your text in the whole worksheet is now in wrap format.
Read More: Excel VBA: Autofit Row Height with Wrap Text (5 Effective Ways)
Conclusion
To sum it up, the question “how to make text automatically wrap around in excel” is answered here in 5 different ways. Starting from using the formatting tool to the ribbon tool, breaking the line, we used VBA at last. Among all of the methods used here, Using the Ribbon tool is the easiest and most time-saving one. The VBA process is also less time-consuming and simplistic but requires prior VBA-related knowledge. Other methods don’t have such a requirement.
For this problem, a macro-enabled workbook is available for download where you can practice these methods.
Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.
