MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Replace Destroys Formatting

October 23, 2017 - by Bill Jelen

Replace Destroys Formatting

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.

Find & Replace
Find & Replace

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.

Replace All
Replace All

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.

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast
  • episode 2054 replace keeping in cell
  • formatting hey welcome back to MrExcel
  • NetCast I'm bill gel and I'm podcasting
  • all my tips in this book click that I on
  • 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
  • right so you eat a double click or press
  • f2 you can select those characters once
  • those characters are selected you can
  • you know maybe apply a color you can
  • press control five for strike through
  • control B for bold control I for italics
  • all kinds of great stuff that you can do
  • all right but here's the hassle if you
  • would later use finder or place and
  • we're gonna change puppy to dog so ctrl
  • H find a recurrence of puppy and change
  • it to a dog and replace all you see that
  • we've lost the formatting that 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 all
  • right so god wow the theme this whole
  • week is that other fine program and the
  • office suite switch over to Microsoft
  • Word ctrl-v to paste ctrl h for finding
  • replace changeover accounts 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
  • eye on 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 intro cell
  • formatting to change the color
  • one word and excel and then later use
  • Find and 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 all right hey I want to thank
  • you for stopping by we'll see you next
  • time for another net cast from MrExcel

Download File

Download the sample file here: Podcast2054.xlsm

Title Photo: Magnascan / Pixabay