Replace Formatting
October 20, 2017 - by Bill Jelen
In Excel, you can mix formatting in a cell. Some words can be bold and some can be red. But if you do a Find & Replace, the formatting will be lost. Here is one way to deal with that.
Technique 6
Change Formatting of Words Within Excel
If you have sentences of text in Excel, it is possible to select one word while in Edit mode and change the color of that word. But globally change the color of all occurrences of the word in Excel is tedious. Instead, paste the data to Word and press Ctrl + H. Change dog to dog. Click More>> and then Format, Font. Choose Red. Click Replace All. Copy from Word and paste back to Excel.
Watch Video
- Changing all occurences of "dog" in Excel to red and bold.
- 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
- You want to change all occurences of the word dog to red & bold
- Copy from Excel.
- Paste Special to Word as Values
- Put the insertion point before the pasted text
- Ctrl + H for Find & Replace
- Click More... to get to the format button
- While on the Replace With, click Format, Font, Red, Bold
- Replace All
- Copy from Word
- Paste to Excel
Auto-Generated Transcript
- Learn Excel from MrExcel Podcast
- episode 2053 bold red dog hey welcome
- back to MrExcel NetCast I am
- podcasting this entire book click the
- eye on the top right hand corner to get
- to the playlist alright our goal today
- is to change every occurrence of the
- word dog to be bold and red oh this is
- going to be horrible in regular Excel we
- would have to double click the cell
- select those characters and then read
- and bold like that would take forever so
- this is another one of those things
- thanks to katie sullivan and the word
- team paste a word next I want to paste
- as text so there we go ctrl H replace
- dog with dog but in this one the second
- one we're going to click more and in
- more while the pointers here go into
- format so format fonts change the font
- color to red change the style to bold
- click OK and then we click replace all
- perfect alright so everything is now
- fixed copy the data from word ctrl c go
- back to excel paste in place and we have
- done the impossible this tip and a lot
- more tips are in this book click the eye
- on the top right hand corner to get to
- that book again our goal here is to
- replace the formatting of one particular
- word using find and replace Excel can't
- do that Microsoft Word can so copy from
- Excel paste special to word as values
- ctrl H click the more button to get to
- the format and then while you're in that
- replace with click format font red bold
- replace all copy from word paste back to
- excel and you're done hey thanks for
- stopping by we'll see you next time for
- another NetCast from MrExcel
Download File
Download the sample file here: Podcast2053.xlsm
Title Photo: Mr Incognito / Pixabay
