Chuck has a spreadsheet where he has bolded the client name in a dozen cells. Unfortunately, only a portion of the cell is bolded and not the whole cell. The Excel 2007 Edit-Replace can not deal with this and changes the entire cell to bold. Episode 915 will show you a solution.
This video is the 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 video is the 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, a question sent in by Chuck from Virginia.
Chuck is doing something interesting here in Excel.
He's using the ability to take part of a cell and bold part of the cell.
Now, how do we do that?
Generally, you're going to go into the formula bar and choose the characters that you want to make bold, and then Ctrl+B. Or, you know, if you're in Excel 2007, you can use that new mini bar that pops up, and use that to bold just part of a cell, but not all of a cell.
So Chuck uses this method, but he's running into a lot of problems because, unfortunately, that "ABC Company" is text that he now needs to change.
So he chooses those cells and goes into Edit, Replace, and changes ABC Company to XYZ Company.
And even if we choose a format here and say that we want to change the bold into bold, unfortunately the Edit, Replace, does not deal with characters that are formatted as bold.
So actually, what we could do is Clear Find Format, now try and Replace All, and look and see what happens.
They can't format just those characters; they end up formatting the entire cell.
So anywhere where ABC Company was, it now gets changed to XYZ Company.
You can just imagine how frustrating this is, because Chuck probably has 15 occurrences of that name.
So here's the solution.
Let me undo, undo, there we go-- so we're back to this.
I'm going to take that text and copy the text, and I'm going to switch over to Microsoft Word.
And in Microsoft Word, I can now paste it-- shows up as a table, we want to leave it as a table.
Use Edit, Replace, here; change ABC Company to XYZ Company; and again, here I'm going to choose formats for both of these.
So I click in here, went into Format, Font, Bold, clicked in the second one; again, go into Format, Font, choose Bold, click OK; Replace All, and you'll see that now XYZ Corporation is in bold.
And the most important part, can we roundtrip it-- can we take this data back and paste it into Excel?
And the great news is that we can.
So if you need to take part of those cells, the ABC Company that were in bold, change it to XYZ that's in bold.
The trick is to go to Microsoft Word.
Now, this is a little scary; this is, like, the second time in two weeks that I've talked about Microsoft Word on the Excel podcast.
I mean I'll be thrown out of the Excel MVP program; we all think that, basically, Microsoft Word is just a silly little add-in for people who can't manage to type their letters in Excel.
But here is a second instance where we had to take something over to Microsoft Word to solve the problem in Word.
It's, like, crazy that I've had to go over to Word now, twice, in a couple of weeks.
But, hey, today is a December 23rd, taking tomorrow and Christmas day off, so I want to wish you and your family an excellent holiday.
We'll see you again on Friday, December 26.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
Today, a question sent in by Chuck from Virginia.
Chuck is doing something interesting here in Excel.
He's using the ability to take part of a cell and bold part of the cell.
Now, how do we do that?
Generally, you're going to go into the formula bar and choose the characters that you want to make bold, and then Ctrl+B. Or, you know, if you're in Excel 2007, you can use that new mini bar that pops up, and use that to bold just part of a cell, but not all of a cell.
So Chuck uses this method, but he's running into a lot of problems because, unfortunately, that "ABC Company" is text that he now needs to change.
So he chooses those cells and goes into Edit, Replace, and changes ABC Company to XYZ Company.
And even if we choose a format here and say that we want to change the bold into bold, unfortunately the Edit, Replace, does not deal with characters that are formatted as bold.
So actually, what we could do is Clear Find Format, now try and Replace All, and look and see what happens.
They can't format just those characters; they end up formatting the entire cell.
So anywhere where ABC Company was, it now gets changed to XYZ Company.
You can just imagine how frustrating this is, because Chuck probably has 15 occurrences of that name.
So here's the solution.
Let me undo, undo, there we go-- so we're back to this.
I'm going to take that text and copy the text, and I'm going to switch over to Microsoft Word.
And in Microsoft Word, I can now paste it-- shows up as a table, we want to leave it as a table.
Use Edit, Replace, here; change ABC Company to XYZ Company; and again, here I'm going to choose formats for both of these.
So I click in here, went into Format, Font, Bold, clicked in the second one; again, go into Format, Font, choose Bold, click OK; Replace All, and you'll see that now XYZ Corporation is in bold.
And the most important part, can we roundtrip it-- can we take this data back and paste it into Excel?
And the great news is that we can.
So if you need to take part of those cells, the ABC Company that were in bold, change it to XYZ that's in bold.
The trick is to go to Microsoft Word.
Now, this is a little scary; this is, like, the second time in two weeks that I've talked about Microsoft Word on the Excel podcast.
I mean I'll be thrown out of the Excel MVP program; we all think that, basically, Microsoft Word is just a silly little add-in for people who can't manage to type their letters in Excel.
But here is a second instance where we had to take something over to Microsoft Word to solve the problem in Word.
It's, like, crazy that I've had to go over to Word now, twice, in a couple of weeks.
But, hey, today is a December 23rd, taking tomorrow and Christmas day off, so I want to wish you and your family an excellent holiday.
We'll see you again on Friday, December 26.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
