Get a month of TabletWise Pro for free! Click here to redeem

Find & Replace – Neutralising Wildcard Characters to Remove Them From Data

2 minutes
Share the link to this page
You need to purchase the class to view this lesson.
One-time Purchase
List Price:  $139.99
You save:  $40
List Price:  د.إ514.17
You save:  د.إ146.91
List Price:  A$201.46
You save:  A$57.56
List Price:  ৳11,880.65
You save:  ৳3,394.71
List Price:  CA$190.27
You save:  CA$54.36
CHF 94.09
List Price:  CHF 131.73
You save:  CHF 37.64
List Price:  kr922.14
You save:  kr263.48
List Price:  €123.82
You save:  €35.37
List Price:  £110.81
You save:  £31.66
List Price:  HK$1,085.07
You save:  HK$310.04
List Price:  ₹10,524.04
You save:  ₹3,007.08
List Price:  RM597.26
You save:  RM170.66
List Price:  ₦54,219.52
You save:  ₦15,492.40
List Price:  kr1,320.98
You save:  kr377.45
List Price:  NZ$212.97
You save:  NZ$60.85
List Price:  ₱6,921.33
You save:  ₱1,977.66
List Price:  ₨23,247.81
You save:  ₨6,642.70
List Price:  S$194.74
You save:  S$55.64
List Price:  ฿4,384.27
You save:  ฿1,252.74
List Price:  ₺961.22
You save:  ₺274.65
List Price:  R2,346.24
You save:  R670.40
Already have an account? Log In


Greetings, everybody. Let me warn you, this is going to be a challenge question with respect to find and replace. In case to the one that you're going to open up through this button of grouping, you'll find some names which may have a status signs inside them. Quite often it happens that when you get the download from a certain e RP system, you get some names, or maybe information or remarks which may contain this asterisk sign. Now if you want to apply a V lookup and try to match up with Jeff abney from another data, you got to eliminate the star marks. Now the challenge is, how using Find and Replace, are you going to eliminate this?

I know what you're thinking, you might be thinking of a scenario where you simply select the data press Ctrl H, and you say find what star and say replace with nothing. Wait, let me show you what's going to happen if I proceed with this technique. I get all the data eliminated. Why? Because wildcard character has the power to include all characters, including including itself. So somehow we got to neutralize the power of this asterisk before we can pacifically eliminate these stars.

So we want to pause the video and try a few techniques, please go ahead. If not, then let me share the solution. I know you cannot hold on to the curiosity. Wait, let me show you the trick. You choose the data you press Ctrl H as usual. Now quite often people do ask me where is Find and Replace from the point of view of button on Microsoft Excel 2007 or 10 or 13.

Well, if you can go to Home tab you'll notice a binary color like sign under Find and Select and that's where you find the Replace button. So I will use Ctrl H or you take the help of this button anyways, in the find watch section must ensure that you enter tilde sign, ti LD, that's what it's called. And generally you find this above the tab key on top of your keyboard. So firstly capslock as you can see from my keyboard, left hand side, then the tab key on top of that there is a tilde sign. So you will have to press Shift and activate that key and it looks like a curly sign. And then you put the asterisk sign.

This has the power to eliminate the wildcard character wildcard power of the star. Once you do that, and if I say replace all i got exactly what I wanted. So if ever somebody challenges you on Excel tricks, you know please ask this question and see yourself when that particular bit. So enjoy this. We will come up with more tricks as we proceed.

Sign Up