Automatically Breaking Text

Summary: Want to convert the text in a cell so that it wraps after every word? You could edit the cell and press Alt+Enter after each word, but there's an easier way using a formula. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007.)

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 versions: 97 | 2000 | 2002 | 2003 | 2007

Make Home Buying Less Stressful! Why make home buying harder than it needs to be? Put your mind at ease—discover all the questions you need to ask to make the best buying decision.

Check out Buying a Home Checklist today!

Helpful Links

Newest Tips

Subscribe

RSS Feeds

Automatically Breaking Text