Episodes 790, 795, 797, and 798 generated a lot of e-mail. We will review different ways to create split cells, copy without borders, and type months and years. Episode 802 will show you how.
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:
Welcome back to the MrExcel netcast. I'm Bill Jelen. It’s episode number 802.
The last 10 episodes, 790 through 799, they generated more mail than anything I've ever done, so we're going to go through all of those.
Episode 790, we talked about how to have a split cell.
Boy, lots of ideas on how to do this.
Brad sent in the idea first.
He said, look, put the 16 up here, put the 24 here, surround all 4 cells with a large bold outline, and then draw in a diagonal line going from one corner to the other corner.
Very similar to Mario, sent in a similar idea here where we're using this time 2 cells, left justifying the top one, right justifying the second one, and, again, that is a line drawing the whole things in.
Now, of course, it's going to be a pain to copy those lines, you know.
I guess what we could do is we could just copy and paste, and it's going to bring the lines over.
The line method though, I think if we have a lot of them, it is going to drive us a little bit crazy.
I like the idea from Tony.
Tony said, hey, we're just going to use simple concatenation.
Make it a fractional display -- not as good-looking as what we originally started with but certainly functional.
So, the formula there, =A1&“ space / space ”&B1, but I think my favorite one was from Stephan.
[ =A1&“ / ”&B1 ] Stephan sent in another formula.
He says, okay, just hide the numbers over there on the side over there in A8 and A9, and then what we're going to do is concatenate A8 and then CHAR10 -- character 10, that's a line feed -- and then use the repeat function, repeat a space 9 times, and then finally A9.
Creates a great-looking view.
[ =$A8&CHAR(10)&REPT(“ ”,9)&$A9 ] In episode 795 and 797, we lamented the fact that it's cool that we can CONTROL click the fill handle and extend it to 2, 3, 4, 5, 6, 7, 8, but then the borders are all screwed up, and we cannot use FILL WITHOUT FORMATTING.
It changes it back to a 1.
Neal said, look, don't put a 1 in there.
Just use =ROW of A1.
Probably the geekiest way in the world to type the number 1, but the advantage is when we copy that down, we can now FILL WITHOUT FORMATTING and it works perfectly, you know, and then sometimes we come up with these really crazy ideas, you know, and someone will write in and say, look, you're just making this way too hard.
Tony said, okay, look, your only problem is up there with the 1.
Just put in the number 2 and then CONTROL click, and drag the fill handle, and copy that down, because the way the borders are drawn, your only issue is with the top one -- although I want to argue here that we lost the bottom border when we did that, but still, sometimes, you can just completely avoid the problem.
And then, in episode 798, I came up with this really complicated way so that way someone could type 08/08 and it would give us August of 2008 instead of August 8th.
A lot of people wrote in and said, you know, hey, you just have to teach the people how to enter data differently.
For example, 7/2008.
5 characters.
It's exactly the same number of characters as you want to type and it does what you want.
Okay.
So, I went back to the original questioner and I said, you know, well, what about this, and she says, look, we have 300 people who have been entering month, month, /, year, year for years in an old legacy system.
I'm never going to be able to train those 300 people, and that's absolutely correct.
Sometimes, it's just a training issue and they want it the old-fashioned way, and so I'll stand by the VBA code.
Well, I really appreciate everyone sending in all these ideas and I appreciate you stopping by.
We'll see you next time for another netcast from MrExcel.
The last 10 episodes, 790 through 799, they generated more mail than anything I've ever done, so we're going to go through all of those.
Episode 790, we talked about how to have a split cell.
Boy, lots of ideas on how to do this.
Brad sent in the idea first.
He said, look, put the 16 up here, put the 24 here, surround all 4 cells with a large bold outline, and then draw in a diagonal line going from one corner to the other corner.
Very similar to Mario, sent in a similar idea here where we're using this time 2 cells, left justifying the top one, right justifying the second one, and, again, that is a line drawing the whole things in.
Now, of course, it's going to be a pain to copy those lines, you know.
I guess what we could do is we could just copy and paste, and it's going to bring the lines over.
The line method though, I think if we have a lot of them, it is going to drive us a little bit crazy.
I like the idea from Tony.
Tony said, hey, we're just going to use simple concatenation.
Make it a fractional display -- not as good-looking as what we originally started with but certainly functional.
So, the formula there, =A1&“ space / space ”&B1, but I think my favorite one was from Stephan.
[ =A1&“ / ”&B1 ] Stephan sent in another formula.
He says, okay, just hide the numbers over there on the side over there in A8 and A9, and then what we're going to do is concatenate A8 and then CHAR10 -- character 10, that's a line feed -- and then use the repeat function, repeat a space 9 times, and then finally A9.
Creates a great-looking view.
[ =$A8&CHAR(10)&REPT(“ ”,9)&$A9 ] In episode 795 and 797, we lamented the fact that it's cool that we can CONTROL click the fill handle and extend it to 2, 3, 4, 5, 6, 7, 8, but then the borders are all screwed up, and we cannot use FILL WITHOUT FORMATTING.
It changes it back to a 1.
Neal said, look, don't put a 1 in there.
Just use =ROW of A1.
Probably the geekiest way in the world to type the number 1, but the advantage is when we copy that down, we can now FILL WITHOUT FORMATTING and it works perfectly, you know, and then sometimes we come up with these really crazy ideas, you know, and someone will write in and say, look, you're just making this way too hard.
Tony said, okay, look, your only problem is up there with the 1.
Just put in the number 2 and then CONTROL click, and drag the fill handle, and copy that down, because the way the borders are drawn, your only issue is with the top one -- although I want to argue here that we lost the bottom border when we did that, but still, sometimes, you can just completely avoid the problem.
And then, in episode 798, I came up with this really complicated way so that way someone could type 08/08 and it would give us August of 2008 instead of August 8th.
A lot of people wrote in and said, you know, hey, you just have to teach the people how to enter data differently.
For example, 7/2008.
5 characters.
It's exactly the same number of characters as you want to type and it does what you want.
Okay.
So, I went back to the original questioner and I said, you know, well, what about this, and she says, look, we have 300 people who have been entering month, month, /, year, year for years in an old legacy system.
I'm never going to be able to train those 300 people, and that's absolutely correct.
Sometimes, it's just a training issue and they want it the old-fashioned way, and so I'll stand by the VBA code.
Well, I really appreciate everyone sending in all these ideas and I appreciate you stopping by.
We'll see you next time for another netcast from MrExcel.
