Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Automatically Breaking Text.
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. 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 (3060) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Automatically Breaking Text.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
Two lists of similar data can be challenging to synchronize. Here are some ways that you can align data in two different ...
Discover MoreIf you need to add dashes between letters and numbers in a string, the work can quickly get tedious. This tip examines ...
Discover MoreAs you are entering data in a worksheet, Excel can monitor what you type and make corrections for common mistakes. One ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2017-04-12 10:38:07
Willy Vanhaelen
@Andy
Turn on wrapping as explained in the last paragraph.
2017-04-11 07:20:58
Andy
This made the "This is my text" into "Thisismytext" with no boxes as you note.
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2019 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: 
Comments