Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. 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, Excel in Microsoft 365, and 2021
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, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Automatically Breaking Text.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Delete a cell or a range of cells, and Excel needs to figure out how to rearrange the void left by the deletion. You can ...
Discover MoreGot some text that is "run together" and needs spaces inserted to improve readability? There are a variety of approaches ...
Discover MoreNeed to get rid of everything in a worksheet except for your formulas? You can do it rather easily by applying the ideas ...
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 © 2024 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. 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