Dak asks how to take a long cell entry and to split it into 20 character entries going down a column without using wrap text. Episode 1102 shows a couple of ways to solve the problem.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Basically you start out with massive amounts of data.
So how are you going to analyze as well let's fire up a pivot table.
Lets see you can solve this problem.
Hey, all right welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in by Dack And oh, this is a challenging one. Dack says that he has words in a cell.
He needs to break those words into exactly 20 characters and have that extend down from the original area so I mean first of all It was tough.
Just Imagine what this would look like. I tried to write in some sentences up here use the LAN function to make sure they were all 20 wide and then I used the CONCATENATE function to put all those together into one big long sentence so there's our concatenate function there. Let's convert that to values and then I first thought, okay Well let's use Edit Fill Justify that will word wrap something and so I chose a nice big area here use Alt E+I+J but unfortunately, because of our proportional spaced fonts. I'll just copy that length function down you see that we get different size text messages in each one. You know just because some like W's are wider so you end up with more or less characters, and it did not work at all but then I said.
OK well, what if we kind of go old-school here.
Let's copy this and we'll do Edit Paste Special Values and I will change the font and all of these to one of the two fixed spaced fonts either Courier or Courier New. So we use Courier New and then use Edit Fill Justify again and check it out that worked out perfectly.
Now before I did that I had to set the column width and it was not to 20 as you might expect I ended up having to test it a little bit and go to 22.1 for a little bit of work there to get it to work.
Also your last character has to be a space, and you see a space gets truncated, so we end up with 19 everywhere and not really the way to go, so then I come back to a formula. I'm sure this is a formula. I got from Mike Gervin at "Excel is fun". Although Mike is going to say they got it from the Mr.Excel message board so it's coming from all over.
I used the MID function here and this is kind of a funny formula in that I selected this whole range of 12 cells and then wrote the MID function that said.
Hey we're going to take D12 and I'm put dollar signs in and then I built a formula that's going to point to basically 1 and then 21 and then 41 and then 61, how did I do that.
I said I want to take the row number of A1 to A13. That's the exact same height as what I selected Minus 1 times 20 so that way for the first cell 0 times 20 is 0 add one to that and say that I want 20 characters, and then finally control+shift+enter and sure enough it splits it out perfectly and let's just test it at a different font.
Again press control+shift+ enter and it doesn't matter what font is, doesn't matter if you end the spaces or not so probably Dack the way to go, is this wild wild array formula that we entered in 13 cells all at once. Ofcourse if this is something you have to do all the time might be easier, just to write a quick little macro that would go through and split things up in 20 character bytes.
But a couple of ways to go Edit Fill Justify or this array formula.
Dack thanks for sending that question in and thanks to you for stopping by.
See you next time for another netcast from MrExcel.
Basically you start out with massive amounts of data.
So how are you going to analyze as well let's fire up a pivot table.
Lets see you can solve this problem.
Hey, all right welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in by Dack And oh, this is a challenging one. Dack says that he has words in a cell.
He needs to break those words into exactly 20 characters and have that extend down from the original area so I mean first of all It was tough.
Just Imagine what this would look like. I tried to write in some sentences up here use the LAN function to make sure they were all 20 wide and then I used the CONCATENATE function to put all those together into one big long sentence so there's our concatenate function there. Let's convert that to values and then I first thought, okay Well let's use Edit Fill Justify that will word wrap something and so I chose a nice big area here use Alt E+I+J but unfortunately, because of our proportional spaced fonts. I'll just copy that length function down you see that we get different size text messages in each one. You know just because some like W's are wider so you end up with more or less characters, and it did not work at all but then I said.
OK well, what if we kind of go old-school here.
Let's copy this and we'll do Edit Paste Special Values and I will change the font and all of these to one of the two fixed spaced fonts either Courier or Courier New. So we use Courier New and then use Edit Fill Justify again and check it out that worked out perfectly.
Now before I did that I had to set the column width and it was not to 20 as you might expect I ended up having to test it a little bit and go to 22.1 for a little bit of work there to get it to work.
Also your last character has to be a space, and you see a space gets truncated, so we end up with 19 everywhere and not really the way to go, so then I come back to a formula. I'm sure this is a formula. I got from Mike Gervin at "Excel is fun". Although Mike is going to say they got it from the Mr.Excel message board so it's coming from all over.
I used the MID function here and this is kind of a funny formula in that I selected this whole range of 12 cells and then wrote the MID function that said.
Hey we're going to take D12 and I'm put dollar signs in and then I built a formula that's going to point to basically 1 and then 21 and then 41 and then 61, how did I do that.
I said I want to take the row number of A1 to A13. That's the exact same height as what I selected Minus 1 times 20 so that way for the first cell 0 times 20 is 0 add one to that and say that I want 20 characters, and then finally control+shift+enter and sure enough it splits it out perfectly and let's just test it at a different font.
Again press control+shift+ enter and it doesn't matter what font is, doesn't matter if you end the spaces or not so probably Dack the way to go, is this wild wild array formula that we entered in 13 cells all at once. Ofcourse if this is something you have to do all the time might be easier, just to write a quick little macro that would go through and split things up in 20 character bytes.
But a couple of ways to go Edit Fill Justify or this array formula.
Dack thanks for sending that question in and thanks to you for stopping by.
See you next time for another netcast from MrExcel.
