Join Dennis Taylor for an in-depth discussion in this video Use REPLACE for formats only, for data only, or for both, part of Excel Tips Weekly.
- [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.
Author
Updated
3/2/2021Released
1/16/2015Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Intermediate
Duration
Views
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.
Related Courses
-
Excel: PivotTables for Beginners
with Dave Ludwig23m 52s Beginner -
Presentation Tips Weekly
with Jole Simmons3h 32m Intermediate -
Excel: Lookup Functions in Depth
with Dennis Taylor1h 8m Intermediate
-
Excel Tips - New This Week
-
Previous Episodes
-
Quick formatting tips8m 9s
-
Create an organization chart8m 56s
-
Auditing9m 1s
-
Adding comments and shapes7m 35s
-
Creating an Excel template7m 57s
-
Adjust banded-row formatting14m 35s
-
Dealing with circular errors8m 20s
-
Sorting tips and shortcuts8m 40s
-
Freeze Panes and Split5m 30s
-
Calculate % of change5m 41s
-
How to adjust names5m 45s
-
Sorting by moving columns4m 16s
-
Using the Solver Add-in4m 29s
-
Create picture links5m 37s
-
Display large values5m 16s
-
Create a powerful macro4m 40s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Use REPLACE for formats only, for data only, or for both