Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 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.
by Allen Wyatt
(last updated February 19, 2020)
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:
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, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Automatically Breaking Text.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Got too much information in a single cell? Here's how you can use a macro to pull apart that information and put it into ...Discover More
Putting the contents of two cells together is easy. Putting together the contents of lots of cells is more involved, as ...Discover More
Want to make an entry of the same value into a group of selected cells? It's easy to do with just one small change in how ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.