Are you a fan of Edit Fill Justify? If so, watch out if you try to justify particularly long text anything over 255 characters in a cell. Excel fails miserably, and you lost part of your text, without any warning from Excel. Episode 567 shows you the details.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Hey, in my books I talk about a cool trick called Edit fill justify.
It edit fill justify is a great way to basically fill in to word wrap some text.
So, here I have a bunch of texts in column a.
If I want to make this fit from columns let's say A through E, I'll select all my text out two columns E and maybe even a few extra rows in case I had some long sentences there and then we can use Edit, Fill, Justify and Excel will word wrap.
Well, Bill wanted to point out that there's a real problem using Edit, Fill, Justify.
Particularly, if any of your sentences are longer than 255 characters long.
So, what I did here is.
I built six sentences that were about 50 characters each, and then I use the CONCATENATE function.
A CONCATENATE function to put all this together into a single bit of text.
What I'm gonna do is, I'm gonna copy that and use Paste Special Values.
So, now that I have basically several sentences that are 300 characters long each and let's say I want to make this try and fit.
I'll select some text and we use Edit, Fill Justify.
Excel silently makes it sound like it actually worked, but what's happened is Excel has silently truncated all of the characters after 255.
So you'll see my original sentence here.
If I go all the way out to the right, the sentence did have this sentence goes to 300 characters and some nines out there.
But, you won't see that in the field text at all.
I mean, it was just silently truncated this is because Edit, Fill, Justify was written back in the days.
When cells can only have 255 characters and even though Excel has expanded to allow 4096 characters in a cell.
They never fixed Edit, Fill Justify, it does not give you any warning whatsoever.
One workaround for this is, if you have several lines of text like that, it's basically to copy the text go over to Microsoft Word and paste now Excel is gonna paste this as a table but, at the end we can open the paste icon and say, Keep Text Only.
Now, you can select all of your texts copy that go back to Excel.
Where we can insert a text box as wide as you need it to be and paste the text and Excel now of course we'll wrap the text inside the text box.
I'm so very long convoluted process the real point of this podcast though is.
If you're a fan of using Edit, Fill, Justify, and just be aware that Excel will silently and without any warning at all lop off all the characters after 255 in each cell.
Thanks to Bill for pointing that out.
If you have a question for the podcast, please feel free to drop us an email bill@mrexcel.com and we'll get to you on a future podcast.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Hey, in my books I talk about a cool trick called Edit fill justify.
It edit fill justify is a great way to basically fill in to word wrap some text.
So, here I have a bunch of texts in column a.
If I want to make this fit from columns let's say A through E, I'll select all my text out two columns E and maybe even a few extra rows in case I had some long sentences there and then we can use Edit, Fill, Justify and Excel will word wrap.
Well, Bill wanted to point out that there's a real problem using Edit, Fill, Justify.
Particularly, if any of your sentences are longer than 255 characters long.
So, what I did here is.
I built six sentences that were about 50 characters each, and then I use the CONCATENATE function.
A CONCATENATE function to put all this together into a single bit of text.
What I'm gonna do is, I'm gonna copy that and use Paste Special Values.
So, now that I have basically several sentences that are 300 characters long each and let's say I want to make this try and fit.
I'll select some text and we use Edit, Fill Justify.
Excel silently makes it sound like it actually worked, but what's happened is Excel has silently truncated all of the characters after 255.
So you'll see my original sentence here.
If I go all the way out to the right, the sentence did have this sentence goes to 300 characters and some nines out there.
But, you won't see that in the field text at all.
I mean, it was just silently truncated this is because Edit, Fill, Justify was written back in the days.
When cells can only have 255 characters and even though Excel has expanded to allow 4096 characters in a cell.
They never fixed Edit, Fill Justify, it does not give you any warning whatsoever.
One workaround for this is, if you have several lines of text like that, it's basically to copy the text go over to Microsoft Word and paste now Excel is gonna paste this as a table but, at the end we can open the paste icon and say, Keep Text Only.
Now, you can select all of your texts copy that go back to Excel.
Where we can insert a text box as wide as you need it to be and paste the text and Excel now of course we'll wrap the text inside the text box.
I'm so very long convoluted process the real point of this podcast though is.
If you're a fan of using Edit, Fill, Justify, and just be aware that Excel will silently and without any warning at all lop off all the characters after 255 in each cell.
Thanks to Bill for pointing that out.
If you have a question for the podcast, please feel free to drop us an email bill@mrexcel.com and we'll get to you on a future podcast.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
