How to Wrap Really Long Text in Excel

Hey, welcome back to the Mr. Excel net cast, I am Bill Jelen. We start out with massive amounts of data and they say, ‘how we are going to analyze this?’ Well, let us fire up the activity table and see if we can solve this problem. Back to Mr. Excel net cast. I am Bill Jelen. Great question, they were sent by Ann. Ann, I was in one of my seminars in Stevens Point so she is from Wisconsin and as she says, ‘hey! A vendor sent me a file. It has about 250 words in a single cell. I can’t see all of the words in the cell, unless I select that cell. All right, so Ann is using Word Wrap obviously. So let’s go select that cell and go to Format Cells and turn on Wrap text, Vertical Top; that is great, click okay. Now I know because I set this up that there are 512 words here and you can see that we can see down about word 143, so I decided all right, I am going to make this cell wider to allow more of the words to fit and it is weird. It’s ending around word 161 and when I click in there, I can see that there are more words but when I click away from the cell, it’s only showing word 161 and initially my reaction was, okay, well you know, were having a problem with it—adjusting the Row Height but you can see that they went to the maximum 409.5 and it just is not fitting. All right, this is a problem that we’ve had in previous versions of excel. Now, let’s see it was going up to about word 130. So what we have to do is click in the cell right before the words that we can not see; word 131. And press Alt-Enter. Alt-Enter will actually allow us to see more of the cell. So this is going to be very tedious for me now. Let’s go though and keep using Alt-Enter at various spots but in fact that is a solution that will allow us to see more of the cells. So a second ago, we can always see that word 131, let’s check it out now. You know now, yes, we can see more and if we make it taller, we can continue to see more. So very painful solution back in Excel 2003 and earlier it’s just to keep going through and using that Alt-Enter trick to get Excel to show you more words be on limit. Now in Excel 2007, looks like they took some great steps to solve this, now this is the amazing expanding formula bar. We close that up here and when we turn on Wrap text, we can eventually get to the point if we make the cell wide enough to see all of the words out to the end. If it’s not wide enough, you run into that limit of the cell is going to be 409 rows high you know and then what can you do? I don’t know. Now, one work around to this and this is so strange, in the last two weeks now, I have talked about a text box, three times after hardly ever talking about text boxes at all is replace that cell with a text box. So insert shapes. I’m just going to draw a big, huge text box in here and inside of that text box, we can paste as much text as we want. So here from this cell, I’ll choose everything from the formula bar, Ctrl-C to copy and then go back to my Text box and click inside Ctrl-V to paste, choose a nice small font size. And you can get all that text to appear whether you are in the cell or not in the cell. Now again, this would be tedious if you had more than a few cells with all these words but it is a great way to get everything show up all the time. Okay so, if you are in Excel 2003, Text box is one solution or using Alt-Enter at various spots. If you are in Excel 2007, you just need to go to the nice small font, really wide column and maximize the height to see all those words. There you have it, great question from Ann. I want to thank you for stopping by. We will see you next time for another net cast from Mr. Excel. We’ll thanks for stopping by. We’ll see you next time for another net cast from Mr. Excel.