Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Automatically Breaking Text.
Written by Allen Wyatt (last updated January 15, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365
Have you ever had a string in a cell that you wanted to wrap after every word? The normal way of doing this would be to press F2 and edit the string. You would delete each space and then press Alt+Enter to add a new line character.
There's an easier, less manual method of doing this, however—just use the SUBSTITUTE function. Suppose cell A1 contained "This is my text." Enter the following into another cell:
=SUBSTITUTE(A1," ",CHAR(10))
What this results in is the text of cell A1 with small boxes where the spaces were (earlier versions of Excel) or no spaces in the text (later versions of Excel). Turn on wrapping for the cell (done in the Format Cells dialog box) and each word appears on a different line, just as you wanted.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11233) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Automatically Breaking Text.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Paste information in a worksheet, and you may end up with Excel placing it into lots of different cells. If you want it ...
Discover MoreImporting data into Excel that was generated in other programs can have some interesting side effects. For instance, you ...
Discover MoreHave a worksheet in which there may be entire columns that are duplicates of each other? If you want to delete those ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2022-01-18 09:30:47
Kathy Dennis
Thank you. As a basic excel user, visual examples of the before and after product would be helpful.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here:
Comments