课程: Excel Essential Training (Microsoft 365)

Finding and replacing data

课程: Excel Essential Training (Microsoft 365)

开始一个月试用

Finding and replacing data

“

- [Instructor] When you're looking for data within a list, sometimes it would be helpful to use a feature found on the home tab, off to the right, called Find & Select. At other times, you might consider using Excels filter, and that's covered in a later movie in this course. Find & Select. And before using this, consider where you're trying to look. That'll shorten the time it takes to find information. If I'm looking for somebody named Rick, and that's not his last name, if it were the last name, I'd simply sort this data based on what's in column A and scroll to that part of the alphabet. But I'm looking for a certain Rick. I'm not sure which one it is. I'll click column A and then go to that icon on the home tab, Find & Select. Find. So we're looking for Rick here. Now, for the moment, I don't care if it's upper or lowercase. That should work okay. We'll simply try and Find All, and it jumps to the first one. There's Rick Combs, row 298, but here's a list right here. And we might be surprised at first because R-I-C-K, even when it's embedded in other words like in Erickson and in Strickland, we found that too. But the list here is sufficiently short. We probably found the one that we want here. Now there's an Options button here, and depending upon who might have been using your computer or this feature most recently, possibly the Options button has been clicked. And we've got some additional choices here too. Sometimes we're looking for data that's only within the entire cell. In other words, there's nothing else there. So switching gears a bit here, let's scroll back up top. Imagine if we're looking for people who only have medical benefits. That's the letter M by itself. So selecting column H, Find & Select. And Excel remembers what we had done previously, but this time we're using M. Match entire cell contents. If we're looking only for those entries where M is present but no other letters are, we'll choose Match entire contents. Let's Find All. It says we found 62 of them. Now if we didn't check the box, I'll uncheck it now, click back on column H and Find All again. Now we've got 296 of them. So always be alert to this. And when you come to this dialog box, remember it isn't always expanded, so click the Options box. Also, Excel remembers previous searches. So sometimes you don't remember or you wouldn't have known what occurred earlier. So I recommend always checking this and just make sure some of these boxes here that you wouldn't want checked are cleared. Now sometimes you want to replace data. Let's imagine this scenario where this company is saying, "You know, we've got some people that are Half-Time, but some people are point four time, others are point six. We want this to be Part-Time." So every time we see the word Half here, we want to replace that with the word Part. So we'll select column D, Find & Select, and this time, Replace. And we don't want to find the letter M, we're looking for Half. And every time we see Half, we want to replace it with Part. And we certainly don't want to match entire cell contents. The word Half never appears by itself. And we'll simply do a Replace All, and Excel will tell us how many times that occurred. All done, we made 118 replacements. And you can see there over in row seven and eight, we see Part-Time and certainly others as well too. So it's an easy to use feature, and it always works a bit more smoothly if you narrow down the search. And always be alert to the idea that when you come to this dialog box, it might or might not be expanded. That's why we have the Options button here. And because Excel remembers previous choices here, I recommend always expanding this to see if there are any because sometimes you get some really strange results if you didn't know what was happening here. So I think you can see it's a powerful tool using this feature for finding data, or in a more expansive way, of replacing data.

内容