David from Spain sends in today's question. When you have a long text cell, it will tend to spill and show up in adjacent blank cells. David notes that you can add an apostrophe to the blank cell to prevent this. In Episode 677, we take a look at a faster way to solve this problem.
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'm Bill Jelen.
Today we have a question send in by David Davidson, Spain.
If you have a question, you can either leave us a voicemail 866-581-0221 or just drop me an email, and we'll get to you on a future podcast.
David has a question about that when you have a long value in one cell.
So, here in Column B, I have this is a long comment, and he says you know it spills over into column C.
It shows up there in Column C, and less there's a value in Column C and he says I don't want it to show up in Column C.
So, I'm very frustrated.
I have to go through and basically put an apostrophe there that creates a blank cell, and that will prevent it from showing up in Column C. Is there an easier way?
Well, you know there's a couple of things you can do it.
The first one is probably not what he wants to do is to select all the Data in Column B. and turn on wrap text.
So, if we go to Format Cells, and then alignment and wrap text that will prevent things from going over to Column C.
But as you can see it's a really horrible looking result I mean pretty much we have to go through and use vertical line top everywhere just to make it acceptable vertical line, Top.
So, I don't think that this is necessarily going to solve this problem.
So, let me go back to Column B, and turn off wrap text again and back to the original problem.
So, I use the exact same method. I put the apostrophe in Column C to prevent it from showing up, but a faster way to do that maybe is to select all the blanks at once.
So, if I select everything in Column C, and then use the Go To. So, F5 is Go To and then Special, select all Blanks that will select all the cells that happen to be blank and then we just hit the apostrophe and to enter that apostrophe in every cell in selection.
Control+Enter will go through and fix that very, very quickly.
So, you don't have to enter all the apostrophes one by one by one, but you still kind of have to use that apostrophe to prevent excel from spilling the cell over into the next column.
So, thanks to David for sending in.
Thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today we have a question send in by David Davidson, Spain.
If you have a question, you can either leave us a voicemail 866-581-0221 or just drop me an email, and we'll get to you on a future podcast.
David has a question about that when you have a long value in one cell.
So, here in Column B, I have this is a long comment, and he says you know it spills over into column C.
It shows up there in Column C, and less there's a value in Column C and he says I don't want it to show up in Column C.
So, I'm very frustrated.
I have to go through and basically put an apostrophe there that creates a blank cell, and that will prevent it from showing up in Column C. Is there an easier way?
Well, you know there's a couple of things you can do it.
The first one is probably not what he wants to do is to select all the Data in Column B. and turn on wrap text.
So, if we go to Format Cells, and then alignment and wrap text that will prevent things from going over to Column C.
But as you can see it's a really horrible looking result I mean pretty much we have to go through and use vertical line top everywhere just to make it acceptable vertical line, Top.
So, I don't think that this is necessarily going to solve this problem.
So, let me go back to Column B, and turn off wrap text again and back to the original problem.
So, I use the exact same method. I put the apostrophe in Column C to prevent it from showing up, but a faster way to do that maybe is to select all the blanks at once.
So, if I select everything in Column C, and then use the Go To. So, F5 is Go To and then Special, select all Blanks that will select all the cells that happen to be blank and then we just hit the apostrophe and to enter that apostrophe in every cell in selection.
Control+Enter will go through and fix that very, very quickly.
So, you don't have to enter all the apostrophes one by one by one, but you still kind of have to use that apostrophe to prevent excel from spilling the cell over into the next column.
So, thanks to David for sending in.
Thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
