Replace Partially Bold Cells

October 16, 2023 - by Bill Jelen

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.

Find and Replace is not kind when you have already formatted *part* of a cell. Here, long text is in a cell with a few words in bold. Try to change ABC to XYZ... — Figure 1369. Do a replace all and the bold will be lost.

And the bold words are lost. The ability for format part of a cell but not all of the cell is find, except the Find and Replace tends to cause the formats to be lost. — Figure 1370. 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.

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.

You could copy the Excel text to Word. Do the Find and Replace there, and the bold words continue to be bold. Copy from Word and back to Excel. — Figure 1371. Do the Replace in Word.

4. Use Ctrl+H. Change ABC to XYZ. Click Replace All. Word keeps the bold.

After the find and replace in Word, the bold ABC is now a bold XYZ. — Figure 1372. 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.

Copy from Word and back to Excel. The bold characters remain bold — Figure 1373. The data is pasted back to Excel correctly.

7. Don’t forget to close Word.

This article is an excerpt from Power Excel With MrExcel

Title photo by Isabella Fischer on Unsplash