Excel - How to Replace without Clearing in-cell Formatting - Episode 2054

Microsoft Excel Tutorial: Replace Part of a Cell without Breaking Character Formatting within a cell.

Welcome to MrExcel Podcast, Episode 2054! In this episode, we will be discussing a common issue that many Excel users face - the loss of formatting when using the Find & Replace function. As we all know, Excel allows us to format specific parts of a cell, such as changing the font color or applying bold or italics. However, when we use Find & Replace to make changes, the formatting is lost. But fear not, because in this video, we will show you a simple workaround to keep your formatting intact.

To begin, let's take a look at the problem. We have a list of data with some cells containing the word "puppy". We want to change all instances of "puppy" to "dog" using Find & Replace. However, when we do this, we notice that the formatting we applied to the word "puppy" is lost. This can be frustrating, especially if you have spent a lot of time formatting your data.

But here's the solution - switch over to Microsoft Word. Yes, you heard that right. Microsoft Word can actually help us preserve our formatting when using Find & Replace. Simply copy and paste your data from Excel to Word, make the changes using Find & Replace, and then paste it back to Excel. Voila! Your formatting will remain intact. This is a great tip from the Word team and it just goes to show that sometimes, Word can be better than Excel in certain situations.

And that's not all, this video is just one of many tips that you can find in my book. Click the "i" in the top-right hand corner to access the playlist and purchase the book. It's filled with spicy tips and tricks that will make your Excel experience even better. So don't forget to check it out!

Thank you for tuning in to this episode of MrExcel Podcast. I hope this tip has helped you overcome the frustration of losing formatting when using Find & Replace in Excel. Don't forget to subscribe to our channel and hit the notification bell to stay updated on our latest videos. And as always, if you have any questions or suggestions for future episodes, leave them in the comments below. See you next time for another netcast from MrExcel!

#excel
#microsoft
#microsoftexcel
#exceltutorial
#evergreen
#excelformatting
#betterinword

This video answers these common search terms:
how to replace the formatting in excel
how to find and replace formatting in excel
how to find and replace cell format in excel
excel find and replace clears formatting
prevent replace from clearing formatting in Excel
why excel replace clears in-cell formatting
how to find replace font in excel
how to replace text in excel with another text
how to replace portion of excel text
how to replace words with another word in excel

Table of Contents:
(00:00) Weird problem with cell formatting
(00:23) Formatting characters in a cell
(00:43) Issue with Find & Replace in Excel
(01:09) Solution using Microsoft Word
(01:53) Another tip from the Word team
(02:20) Clicking Like really helps the algorithm

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.

[sergeylyakh]

Thanks for the neat trick, Bill!

One issue I ran into with this: if any one of those cells contained a new line character (Alt+Enter) → it would paste into Word correctly, but it would paste back into Excel as a new row.
I haven't found a workaround for this. :/

[ganeshs]

this is one serious drawback with excel. currently am doing something similar within one cell, able to create the required formatting, but fails to delete or replace the junk that is left over, any function would destroy the formatting and range.characters.delete or range.characters.text will only work for a cell less than 256 characters. is there a work around for this? i need the formatting to stay inside one single cell. ?

[georgezhao]

Word destroy my alt+enter line break, even though in word it's ^l instead of ^p