Use REPLACE for formats only, for data only, or for both

- [Instructor] Excel's Find and Replace command is ideally and typically designed to deal with content, but you can also focus on format. We might want to replace one format with another format regardless of content and we can actually do both. Sometimes it's a combination of content and format. Let's start with a simple idea. In this worksheet here, after getting approval from higher management, we're going to change the names of some of the departments here and instead of the word and wherever we see it, we want to use that ampersand symbol found on the number seven key on the keyboards.

It'll make these entries a bit shorter. And we see the word and, for example, in cell C2, also in C5, six, 14, 15, 17, quite a few places here. Let's take care of all these at once. This is a list of about 700 entries or so. So, let's select column C and then, on the Home tab, far right button, Find and Select, and then Replace. Sometimes when you see this dialogue box, you won't see some of the choices we're seeing now. If I click Options, it possibly will look like this.

In some cases, you will want to choose Options, but for the moment, we're only concerned with content. If it looks like this, fine and if it is in the larger view, that's okay, too. We're just focused on content. Every time we see the word and, we want to replace it with that ampersand symbol, the and symbol, and let's replace all of these. Alright, 163 of them, that looks good. We click OK and then Close, but what might we see? And you might've seen it. In cell C10 here, it looks like the word and was actually within that word, we didn't think about that.

That was Mail Handling Services and that's what it should be, and we don't want to change that, so let's undo with Control+z. Undo that replace that we just did. Column C is selected, let's go back to Find and Select, and Replace, and this time, every time we see space A-N-D. Of course, that will not pick up A-N-D when it's embedded within a word. Space, A-N-D, space. And then also, for the replacement part, space, ampersand symbol, space.

Replace All, 153 replacements instead of 163. Close this, that looks a lot better, and there's Mail Handling Services. And of course, we do see the ampersand in these other cases. Now let's focus just on the format. We want to get rid of the yellow that we see as a background color on these rows. Let's just select all the data, Find and Replace, Replace, and here we must see the additional options, let's choose Options, and wipe out any content that we see here.

We're not concerned about the word and anymore or the ampersand, get rid of those, and although we don't see any Format here, it's not a bad idea to click the drop-arrow to the right of Format here and if Clear Find Format is available, in other words, it's not grayed out, do make that choice. And same thing here, too, for the other one, this case we don't need to. So, what are we looking for here? A Format here for Find What, we're looking for the format of a yellow background. On the Fill tab, yellow, click OK and we don't want to replace this with any particular format, however, if we choose Replace All, look what happens.

It looks like we lost our data, too. Well, let's click OK, close this and undo this with Control+z, and revisit this. The data's still selected. Find and Select, Replace, we want to replace the yellow format with a format of No Fill. So, under Format here, choose Format, and under Fill, No Color, click OK. This time as we Replace All, we see in the background what's happened, that yellow is gone.

The 2,101, by the way, refers to the actual cell count. 2,101 cells no longer have a yellow background, so that's done and we saw how that works. So, we focused just on format, now let's focus on both. We want to change these entries in column F. Every time we see Half-Time in red, and notice that we do see Contract in red here, Full Time in red, we want to make a change when we see the red font, but only when it's Half-Time, and we're going to change those to be Full Time. So, Find and Select, Replace, and we don't want to be using yellow at all, so as I suggested earlier, click the drop-arrow for Format here, Clear Find Format.

Also here on the Replace Format, clear that as well, too. What are we focused on now? First of all, the content is Half-Time, but only when that's red. So, we are concerned about the format here under Find What. Format, this case, Font, Color, Red. We want to replace Half-Time, but only when it's red, we want to replace that with Full Time and we want to format here, too. we want to make it stand out, we want to keep the red, but make it bold.

Format, Color, Red, but also Bold. So, once again, we are replacing Half-Time, but only when it's red, we're replacing it with the phrase Full Time. We want it to be red and bold. So, as you look at this worksheet right now, it's in rows five and 10, at least on the screen that we can see. Rows five and 10 are going to change, they're going to become Full Time, they will be red and bold. Nothing else that we're seeing in column F, for the moment, will change as we Replace All.

There were 16 replacements throughout this list, but the focus here is on these cells, right here, Full Time, and also right here, row five and row 10, we changed both of those. So, these are just different thoughts, different examples here on how you can use this Replace feature, found on Find and Select, to replace, sometimes content, sometimes format, sometimes both. A valuable tool in making wholesale changes to a large worksheet.