This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Replace Partially Bold Cells
Problem: I have some cells that are partially bold. When I use Find and Replace to change the text in these cells, I am losing the bold.
Figure 1220 Do a replace all and the bold will be lost.
Figure 1221 The bold is lost.
I tried adapting the last topic, choosing Bold as the format in the second box, but then the entire cell become bold whenever the text is found.
Figure 1222 Or the entire cell becomes bold.
Strategy: Excel really does not deal well with cells that are partially formatted. It pains me to say this, but here is an example where Microsoft Word can save the day.
1. Copy your data in Excel.
2. Open Word. (If you’ve never used Word, think of it as an add-in for people who can’t seem to type their letters in Excel).
3. Paste the cells to Word.
Figure 1223 Do the Replace in Word.
4. Use Ctrl+H. Change ABC to XYZ. Click Replace All. Word keeps the bold.
Figure 1224 Word can do a replace without changing the bold.
5. In Word, use Ctrl+A to select all, Ctrl+C to copy. Switch back to Excel with Alt+Tab.
6. Ctrl+V in Excel to paste. The cells are pasted correctly.
Figure 1225 The data is pasted back to Excel correctly.
7. Don’t forget to close Word.
<-Previous Topic Next Topic->
For more resources for Microsoft Excel: