Replace Destroys Formatting
October 23, 2017 - by Bill Jelen
Using Find and Replace will screw up your in-cell formatting. What do I mean by that? Let's say that you have 25 letters in Excel and only the third word is bold or red. Find and replace any of the words in the cell and you will lose the formatting. This article describes a workaround.
Technique 7
Replace While Keeping Character Formatting
Word also handles a similar problem: replacing text but leaving the text formatting as it is. Below is a survey about the best pet. Someone has highlighted certain words within the text.
Use Ctrl + H to do a Find and Replace. When you use Replace All, if a sentence was changed, your in-cell formats will be lost. In the figure below, the strikethrough remains in the first row because that row did not have an occurrence of the word puppy and thus was not changed.
To keep the formatting in the original text, copy to Word. Do the Replace in Word. Copy from Word and paste back to Excel.
On behalf of the entire Word team, Katie invites all of you Excellers who still type your letters in Excel using Fill Justify to come on over and give Word a spin.
Watch Video
- 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.
Video Transcript
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.
Download File
Download the sample file here: Podcast2054.xlsm
Title Photo: Magnascan / Pixabay
