Find & Replace – Using Wildcard Character ( * )

Advanced Excel Crash Course Section 12: Data Cleaning
3 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€92.26
List Price:  €129.16
You save:  €36.90
£78.90
List Price:  £110.47
You save:  £31.56
CA$135.76
List Price:  CA$190.06
You save:  CA$54.30
A$153.66
List Price:  A$215.14
You save:  A$61.47
S$134.30
List Price:  S$188.03
You save:  S$53.72
HK$782.11
List Price:  HK$1,094.99
You save:  HK$312.87
CHF 88.79
List Price:  CHF 124.31
You save:  CHF 35.52
NOK kr1,070.17
List Price:  NOK kr1,498.28
You save:  NOK kr428.11
DKK kr688.01
List Price:  DKK kr963.24
You save:  DKK kr275.23
NZ$165.58
List Price:  NZ$231.83
You save:  NZ$66.24
د.إ367.20
List Price:  د.إ514.10
You save:  د.إ146.89
৳11,008.59
List Price:  ৳15,412.47
You save:  ৳4,403.87
₹8,303.29
List Price:  ₹11,624.95
You save:  ₹3,321.65
RM473.19
List Price:  RM662.48
You save:  RM189.29
₦156,506.34
List Price:  ₦219,115.14
You save:  ₦62,608.80
₨28,049.28
List Price:  ₨39,270.12
You save:  ₨11,220.83
฿3,608.38
List Price:  ฿5,051.88
You save:  ฿1,443.50
₺3,233.78
List Price:  ₺4,527.43
You save:  ₺1,293.64
B$503.03
List Price:  B$704.27
You save:  B$201.23
R1,898.90
List Price:  R2,658.54
You save:  R759.63
Лв180.15
List Price:  Лв252.22
You save:  Лв72.06
₩133,999.91
List Price:  ₩187,605.24
You save:  ₩53,605.32
₪366.35
List Price:  ₪512.91
You save:  ₪146.55
₱5,593.09
List Price:  ₱7,830.55
You save:  ₱2,237.46
¥15,066.66
List Price:  ¥21,093.92
You save:  ¥6,027.26
MX$1,690.23
List Price:  MX$2,366.39
You save:  MX$676.16
QR365.81
List Price:  QR512.15
You save:  QR146.33
P1,369.30
List Price:  P1,917.08
You save:  P547.77
KSh13,273.67
List Price:  KSh18,583.67
You save:  KSh5,310
E£4,710.13
List Price:  E£6,594.37
You save:  E£1,884.24
ብር5,696.11
List Price:  ብር7,974.78
You save:  ብር2,278.67
Kz83,441.65
List Price:  Kz116,821.65
You save:  Kz33,380
CLP$94,734.02
List Price:  CLP$132,631.42
You save:  CLP$37,897.40
CN¥719.86
List Price:  CN¥1,007.84
You save:  CN¥287.97
RD$5,933.04
List Price:  RD$8,306.49
You save:  RD$2,373.45
DA13,480.42
List Price:  DA18,873.13
You save:  DA5,392.71
FJ$226.79
List Price:  FJ$317.51
You save:  FJ$90.72
Q783.08
List Price:  Q1,096.35
You save:  Q313.26
GY$20,985.05
List Price:  GY$29,379.92
You save:  GY$8,394.86
ISK kr13,738.62
List Price:  ISK kr19,234.62
You save:  ISK kr5,496
DH1,007.92
List Price:  DH1,411.13
You save:  DH403.21
L1,768.33
List Price:  L2,475.73
You save:  L707.40
ден5,685.15
List Price:  ден7,959.43
You save:  ден2,274.28
MOP$807.89
List Price:  MOP$1,131.08
You save:  MOP$323.19
N$1,891.51
List Price:  N$2,648.20
You save:  N$756.68
C$3,691.90
List Price:  C$5,168.80
You save:  C$1,476.90
रु13,305.26
List Price:  रु18,627.89
You save:  रु5,322.63
S/369.78
List Price:  S/517.70
You save:  S/147.92
K378.11
List Price:  K529.37
You save:  K151.26
SAR374.99
List Price:  SAR525
You save:  SAR150.01
ZK2,562.18
List Price:  ZK3,587.15
You save:  ZK1,024.97
L458.93
List Price:  L642.52
You save:  L183.59
Kč2,328.03
List Price:  Kč3,259.33
You save:  Kč931.30
Ft36,496.67
List Price:  Ft51,096.80
You save:  Ft14,600.13
SEK kr1,049.30
List Price:  SEK kr1,469.07
You save:  SEK kr419.76
ARS$85,200.41
List Price:  ARS$119,283.99
You save:  ARS$34,083.57
Bs693.12
List Price:  Bs970.40
You save:  Bs277.27
COP$390,541.29
List Price:  COP$546,773.43
You save:  COP$156,232.14
₡51,032.06
List Price:  ₡71,446.93
You save:  ₡20,414.86
L2,476.89
List Price:  L3,467.75
You save:  L990.85
₲731,475.55
List Price:  ₲1,024,095.03
You save:  ₲292,619.48
$U3,846.42
List Price:  $U5,385.15
You save:  $U1,538.72
zł398.58
List Price:  zł558.03
You save:  zł159.45
Already have an account? Log In

Transcript

Hi, I hope you're enjoying the video session so far. In this video session we'll talk about finding replace, and we see its importance in the context of data cleaning. In fact, I have to start with the data which is going to illustrate how is this going to be used. For example, if I have a set of names with certain extra data with respect to their domains, their location in which they are working, for example, the first guy is Milo Salaam, he is allocated to the Asia Pac team, and the second one belongs to Latin America team. Now if you just want to delete the slash input, I don't want this part. How do I do that quickly with a help of Find and Replace, I will choose the sample data I'll press Ctrl H H for horse.

Once I do that, it says find what I will say slash I will see i n and then I will see slash closed. That means the same backstroke Now replace it what nothing when I do that replace all notice amongst the chosen data, all the three instances of slash in slash goes away, it says we have made three replacements. Now this was basic Find and Replace, which you may have used in the Word document also, but I want us to discuss is the use of wildcard character, and we'll see how much this particular wildcard character amplifies the power of mind and replace. Let's find out assume that you want to eliminate everything after the first slash including the first slash or backstroke as some people call it. Now, I noticed the common characters aren't present. For example, the first one differs from the second one in terms of suffix.

So I want to choose the data. Let's say halfway through, press Ctrl H. Now in the find words section, let me put slash forward slash poodle star mark or asterisk as it is called Sometimes now once I do that, and I see replaced with nothing, as I click on replace all notice what is going to happen? Well, it says all done we made five replacements and you notice slash onwards everything gone. So star or asterisk means it can pick up any number of character. The reason why we gave a slash before star is we did not want to eliminate the full set of characters because if I say Ctrl H and if I simply say star, it will replace all the data with nothing, which is what I did not want. So, I need to give an identifier through which Excel can identify from which character the data must be eliminated.

In fact, if I give one more example, let's say Ctrl H, and I see add three star Can you guess what is going to happen if I press the Replace All button? Yes agreat onwards, everything will be going away. So replace all there you go. This is wildcard character usage in Find and Replace. We'll see more such tricks in the next video.

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.