A question from Twitter asked how to have long text on an invoice extend down many rows. In today's podcast, Bill looks at three different ways.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel form MrExcel podcast episode 1772.
Stretched Long Text Down Rows.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, today's question actually started out on twitter and this is a great way to get questions to me if you just send out a tweet put, @MrExcel in there. I'll be guaranteed to see it or if you want to roll the dice #Excel, I only see those during you know coffee breaks or when I'm bored and so, putting @MrExcel in the tweet will help.
This question came in someone was trying to figure out how to stretch text across rows and turns out.
She had an invoice and you know pretty, fairly simple invoice.
We have a quantity, but then a really long description we want this description to go down these rows.
Now, let's see just exactly how long this description is equal length of this fake description here, 424 characters.
All right! So, my initial reaction is well we're going to use Edit, Fill, Justify which that's Excel 2003 was that Edit, Fill, Justify.
Now, it's I just Home, Fill, Justify.
So, Home and then we go on the Fill, open it up and choose Justify, but this command is so, so old it doesn't work for I think more than 255 characters.
Let's add all these up over here.
So, when you do Fill, Justify, it just simply truncates everything and we only get let's see Alt+Equals when I get the 255 characters or probably spaces there.
So, you lose a lot.
All right, So, Edit, Fill, Justify, your Home, Fill, Justify is not going to work.
Boy! This pains me I'm now, stuck with the only thing I can think of it's going to work, is to select that long text and select a whole bunch of cells like this and we're going to do control+1 to get the Format Cells, on the Alignment tab we're going to turn on Wrap text and then we're going to Merge cells, like this will also want to do Horizontal, I can stay at General, but the vertical has to go to Top.
Let's see, how that looks click OK, there we go you now actually get to see all of that description, but unfortunately we had merged cells this is really the only time that I think it's acceptable merge cells when we're trying to get data to go down vertically we're trying to get it to go across, then you don't want to merge cells.
You would just do Control+1 and under Horizontal, Center Across Selection.
We can't Center Across, a Vertical selection that doesn't work.
So, we're stuck with this.
Of course if you don't ever want to merge cells, let's undo that.
What we could do is choose that one cell, control+1 and just turn on Wrap text, click OK, but then we're going to have to go through all of the neighboring cells, everything else in the row and move that to the top and I guess that would work as well provided you didn't have anything else out here to the side.
So, there you go.
Three ways to go, Fill, Justify, definitely will not work.
Merge cells would work or Wrap text and then adjusting the neighboring cells.
All right! Well hey, I wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
There's a lot of different ways to connect with us here at MrExcel as I mentioned twitter just include @MrExcel in your tweet, and will take a look and help you on the spot.
Learn Excel form MrExcel podcast episode 1772.
Stretched Long Text Down Rows.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, today's question actually started out on twitter and this is a great way to get questions to me if you just send out a tweet put, @MrExcel in there. I'll be guaranteed to see it or if you want to roll the dice #Excel, I only see those during you know coffee breaks or when I'm bored and so, putting @MrExcel in the tweet will help.
This question came in someone was trying to figure out how to stretch text across rows and turns out.
She had an invoice and you know pretty, fairly simple invoice.
We have a quantity, but then a really long description we want this description to go down these rows.
Now, let's see just exactly how long this description is equal length of this fake description here, 424 characters.
All right! So, my initial reaction is well we're going to use Edit, Fill, Justify which that's Excel 2003 was that Edit, Fill, Justify.
Now, it's I just Home, Fill, Justify.
So, Home and then we go on the Fill, open it up and choose Justify, but this command is so, so old it doesn't work for I think more than 255 characters.
Let's add all these up over here.
So, when you do Fill, Justify, it just simply truncates everything and we only get let's see Alt+Equals when I get the 255 characters or probably spaces there.
So, you lose a lot.
All right, So, Edit, Fill, Justify, your Home, Fill, Justify is not going to work.
Boy! This pains me I'm now, stuck with the only thing I can think of it's going to work, is to select that long text and select a whole bunch of cells like this and we're going to do control+1 to get the Format Cells, on the Alignment tab we're going to turn on Wrap text and then we're going to Merge cells, like this will also want to do Horizontal, I can stay at General, but the vertical has to go to Top.
Let's see, how that looks click OK, there we go you now actually get to see all of that description, but unfortunately we had merged cells this is really the only time that I think it's acceptable merge cells when we're trying to get data to go down vertically we're trying to get it to go across, then you don't want to merge cells.
You would just do Control+1 and under Horizontal, Center Across Selection.
We can't Center Across, a Vertical selection that doesn't work.
So, we're stuck with this.
Of course if you don't ever want to merge cells, let's undo that.
What we could do is choose that one cell, control+1 and just turn on Wrap text, click OK, but then we're going to have to go through all of the neighboring cells, everything else in the row and move that to the top and I guess that would work as well provided you didn't have anything else out here to the side.
So, there you go.
Three ways to go, Fill, Justify, definitely will not work.
Merge cells would work or Wrap text and then adjusting the neighboring cells.
All right! Well hey, I wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
There's a lot of different ways to connect with us here at MrExcel as I mentioned twitter just include @MrExcel in your tweet, and will take a look and help you on the spot.
