Ari sends in a better solution to podcast 677. If you want to prevent text from long cells spilling over to adjacent blank cells, the technique shown in Episode 686 works like a charm. This method might even clean up spills faster than Bounty...
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
We're gonna go back to episode 677.
That's from January 22nd.
David from Spain sent in a question.
And today, I have a much better solution already sent in and just what I thought to be brilliant on David's problem.
He has long text here in Column B.
Some bags in Column C are filled in.
Other columns aren't.
But it's filled in.
of course it cuts off the long value and that's what he wants.
He doesn't want these extra words spilling over here to Column C.
He just wants to truncate it and I came up with this crazy solution, where we went through and basically, put in blank sales to prevent that from happening.
A much better way to go, and it'll fix the entire worksheet at once.
Select the entire worksheet.
Go to Format Cells and completely counter intuitive.
Turn on Wrap text.
Well that's going to screw everything up, but here's the thing that makes it work.
We'll go back Format>Row>height and set all of those rows to 12.75.
That's the height of the original row in Excel.
Beautiful!
We only see the values that sit there in Column B.
Nothing ever spills to Column C.
And our rows are the right height.
I'll send an Excel master pin out there for that great great solution.
I'll let David know to watch this one too.
Much better than what I came up with back on the 22nd.
So there you have it.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I am Bill Jelen.
We're gonna go back to episode 677.
That's from January 22nd.
David from Spain sent in a question.
And today, I have a much better solution already sent in and just what I thought to be brilliant on David's problem.
He has long text here in Column B.
Some bags in Column C are filled in.
Other columns aren't.
But it's filled in.
of course it cuts off the long value and that's what he wants.
He doesn't want these extra words spilling over here to Column C.
He just wants to truncate it and I came up with this crazy solution, where we went through and basically, put in blank sales to prevent that from happening.
A much better way to go, and it'll fix the entire worksheet at once.
Select the entire worksheet.
Go to Format Cells and completely counter intuitive.
Turn on Wrap text.
Well that's going to screw everything up, but here's the thing that makes it work.
We'll go back Format>Row>height and set all of those rows to 12.75.
That's the height of the original row in Excel.
Beautiful!
We only see the values that sit there in Column B.
Nothing ever spills to Column C.
And our rows are the right height.
I'll send an Excel master pin out there for that great great solution.
I'll let David know to watch this one too.
Much better than what I came up with back on the 22nd.
So there you have it.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
