Hey welcome back to the Mr. Excel net cast I’m Bill Jelen we start out with massive amounts of data. So how are we going to analyze this? We’ll lets fire up with 0:09 and see if we can solve this problem.
Hey welcome back to Mr. Excel net cast I’m Bill Jelen question sent by Dack. Oh, this is a challenging one, Dack says that he has word in a cell, he needs to break those words into exactly 20 characters and have that extend down from the original area.
I mean first of all, just imagine what this looks like. I tried some sentences up here, use the LAM function 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 is our concatenate function there, you can 0:50 to values and then –first of okay, we’ll use edit fill just to justify, that will word wrap something I choose a nice big area here and use all EIJ.
But unfortunately because of our proportional space fonts, it’ll just copy that link function show down. You see that we get different size text messages in each one. 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, what if we kind of go old school here? lets copy this and we’ll so edit pay special values and I will change the font in all 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 having to testing it a little bit and it go to 22.14 a little bit of work there to get it to work, also your last character has to be a space and you see that space gets triangulated so we end up 19 everywhere. Not really the way to go. So then, I come back to a formula. I m sure that this is a formula I got from Mike Gerwin although Mike is going to say that you got it from the Mr. Excel message board so its coming from all over. I used the mid function here. and this is kind of a funny formula that I selected this whole range of 12 cells and then wrote the mid function that said hey I’m going to take D12 and I’m going to put dollar signs in.
Then I built a formula that is going to that’s going to point to basically into 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 that’s why I selected minus 1 times 20 so that way for the first cell zero times twenty is zero add one to that and say that I want 20 characters in that and finally Ctrl +Shift +Enter and sure enough it slits it out perfectly and lets just test it at a different font. Again press Ctrl+ Shift+ Enter and it doesn’t matter what font is, doesn’t matter if you end with spaces or now. So probably Dack, the way to go is this wild, wild ray formula that we add in 13 cells all at once.
Of course this is some thing you have to do all the time, it might be easier to run a little quick Marco and we’ll go through and split things up in 20 character bytes. But a couple of ways to go and if you’ll justify or – this ray formula.
Dack, thanks for sending that question in and thanks for you to stopping by see you next time for another net cast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services