Today's trick is from Katie Sullivan on the Word team
There are a few cases where Microsoft Word can do things better than Excel
It is possible in Excel to format part of a cell by selecting a word in Edit mode and applying formatting.
But, if you use Find & Replace and anything is changed, that formatting is wiped out.
Copy from Excel. Paste to Word.
Do the Replace there.
Copy from Word and Paste Back to Excel. The formatting stays.
There are a few cases where Microsoft Word can do things better than Excel
It is possible in Excel to format part of a cell by selecting a word in Edit mode and applying formatting.
But, if you use Find & Replace and anything is changed, that formatting is wiped out.
Copy from Excel. Paste to Word.
Do the Replace there.
Copy from Word and Paste Back to Excel. The formatting stays.
Transcript of the video:
Learn Excel from MrExcel Podcast, Episode 2054: Replace Keeping in Cell Formatting Hey, welcome back to MrExcel netcast, I’m Bill Jelen.
I'm podcasting all of my tips in this book.
Click that “i” in the top-right hand corner to get to the playlist.
We have a weird, weird problem today.
Alright so, as you know it's possible to format just part of a cell, alright.
So, you either double-click of press F2, you can select those characters.
Once those characters are selected, you can, you know, maybe apply a color.
You can press Ctrl+5 for strikethrough, Ctrl+B for bold, Ctrl+I for Italics, all kinds of great stuff that you can do, alright.
But here's the hassle.
If you would later use Find & Replace, and we're going to change puppy to dog, so Ctrl+H.
Find any reoccurrence of puppy and change it to a dog and Replace All, you see that we've lost the formatting.
Now, we didn't lose the formatting in Row 1 because there was no puppy there.
They didn't make a change but it's in the places that there was a puppy and they did change it.
We've lost all of the intra cell formatting, this is really, really weird.
I'm going to Ctrl+Z to go back, alright.
So, wow!
The theme this whole week is that other fine program in the Office Suite.
Switch over to Microsoft Word.
Ctrl+V to paste, Ctrl+H for Find & Replace, change over occurrence of puppy to dog.
Replace All, made the changes but it doesn’t change our formatting.
From here, of course, copy Ctrl+C, come back to Excel, paste Ctrl+V, and our puppies are now dogs, but we haven't lost the formatting.
This tip and a whole bunch of other spicy tips are in this book.
Click the “i” in the top-right hand corner to buy the book.
Alright, yet another trick from Katie Sullivan and the Word team.
I hate to say it.
There's times where Word is better than Excel.
If you use intra cell formatting to change the color of one word in Excel, and then later use Find & Replace, it's going to wipe out your formatting.
But you can take that data from Excel, paste it to Word, do the replace there and then come back.
Works out great.
Alright, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm podcasting all of my tips in this book.
Click that “i” in the top-right hand corner to get to the playlist.
We have a weird, weird problem today.
Alright so, as you know it's possible to format just part of a cell, alright.
So, you either double-click of press F2, you can select those characters.
Once those characters are selected, you can, you know, maybe apply a color.
You can press Ctrl+5 for strikethrough, Ctrl+B for bold, Ctrl+I for Italics, all kinds of great stuff that you can do, alright.
But here's the hassle.
If you would later use Find & Replace, and we're going to change puppy to dog, so Ctrl+H.
Find any reoccurrence of puppy and change it to a dog and Replace All, you see that we've lost the formatting.
Now, we didn't lose the formatting in Row 1 because there was no puppy there.
They didn't make a change but it's in the places that there was a puppy and they did change it.
We've lost all of the intra cell formatting, this is really, really weird.
I'm going to Ctrl+Z to go back, alright.
So, wow!
The theme this whole week is that other fine program in the Office Suite.
Switch over to Microsoft Word.
Ctrl+V to paste, Ctrl+H for Find & Replace, change over occurrence of puppy to dog.
Replace All, made the changes but it doesn’t change our formatting.
From here, of course, copy Ctrl+C, come back to Excel, paste Ctrl+V, and our puppies are now dogs, but we haven't lost the formatting.
This tip and a whole bunch of other spicy tips are in this book.
Click the “i” in the top-right hand corner to buy the book.
Alright, yet another trick from Katie Sullivan and the Word team.
I hate to say it.
There's times where Word is better than Excel.
If you use intra cell formatting to change the color of one word in Excel, and then later use Find & Replace, it's going to wipe out your formatting.
But you can take that data from Excel, paste it to Word, do the replace there and then come back.
Works out great.
Alright, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
