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 January 15, 2022)
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.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Want to select all the data in a contiguous section of a worksheet? The shortcut discussed in this tip makes it very easy.Discover More
Limiting what can be entered in a cell can be an important part of developing a worksheet that other people use. Here are ...Discover More
AutoComplete can help you to more quickly enter information in a worksheet. How it works, behind the scenes, can affect ...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.