Text Formulas – REPT() & LEN() for Adding Leading Zeroes Before Incomplete Cheque Nos.

Advanced Excel Crash Course Section 12: Data Cleaning
4 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
€91.74
List Price:  €128.44
You save:  €36.70
£78.54
List Price:  £109.95
You save:  £31.41
CA$135.28
List Price:  CA$189.39
You save:  CA$54.11
A$152.35
List Price:  A$213.30
You save:  A$60.94
S$133.70
List Price:  S$187.19
You save:  S$53.48
HK$782.09
List Price:  HK$1,094.96
You save:  HK$312.87
CHF 88.44
List Price:  CHF 123.82
You save:  CHF 35.38
NOK kr1,060.96
List Price:  NOK kr1,485.39
You save:  NOK kr424.42
DKK kr684.80
List Price:  DKK kr958.74
You save:  DKK kr273.94
NZ$164.32
List Price:  NZ$230.05
You save:  NZ$65.73
د.إ367.22
List Price:  د.إ514.12
You save:  د.إ146.90
৳10,977.27
List Price:  ৳15,368.62
You save:  ৳4,391.35
₹8,288.18
List Price:  ₹11,603.79
You save:  ₹3,315.60
RM470.40
List Price:  RM658.58
You save:  RM188.18
₦159,205.14
List Price:  ₦222,893.57
You save:  ₦63,688.42
₨27,945.54
List Price:  ₨39,124.87
You save:  ₨11,179.33
฿3,570.70
List Price:  ฿4,999.13
You save:  ฿1,428.42
₺3,209.07
List Price:  ₺4,492.83
You save:  ₺1,283.75
B$499.58
List Price:  B$699.43
You save:  B$199.85
R1,877.33
List Price:  R2,628.34
You save:  R751
Лв179.79
List Price:  Лв251.71
You save:  Лв71.92
₩133,020.69
List Price:  ₩186,234.29
You save:  ₩53,213.60
₪366.08
List Price:  ₪512.52
You save:  ₪146.44
₱5,560.69
List Price:  ₱7,785.19
You save:  ₱2,224.50
¥14,903.11
List Price:  ¥20,864.95
You save:  ¥5,961.84
MX$1,671.47
List Price:  MX$2,340.12
You save:  MX$668.65
QR364.79
List Price:  QR510.72
You save:  QR145.93
P1,358.93
List Price:  P1,902.56
You save:  P543.62
KSh13,752.81
List Price:  KSh19,254.48
You save:  KSh5,501.67
E£4,780.94
List Price:  E£6,693.51
You save:  E£1,912.56
ብር5,681.93
List Price:  ብር7,954.93
You save:  ብር2,273
Kz83,641.63
List Price:  Kz117,101.63
You save:  Kz33,460
CLP$93,803.57
List Price:  CLP$131,328.75
You save:  CLP$37,525.18
CN¥719.51
List Price:  CN¥1,007.35
You save:  CN¥287.83
RD$5,921.90
List Price:  RD$8,290.90
You save:  RD$2,369
DA13,438.45
List Price:  DA18,814.37
You save:  DA5,375.92
FJ$226.07
List Price:  FJ$316.51
You save:  FJ$90.44
Q780.87
List Price:  Q1,093.26
You save:  Q312.38
GY$20,947.35
List Price:  GY$29,327.13
You save:  GY$8,379.77
ISK kr13,640.79
List Price:  ISK kr19,097.66
You save:  ISK kr5,456.86
DH1,002.59
List Price:  DH1,403.66
You save:  DH401.07
L1,770.35
List Price:  L2,478.57
You save:  L708.21
ден5,647.93
List Price:  ден7,907.33
You save:  ден2,259.40
MOP$805.83
List Price:  MOP$1,128.19
You save:  MOP$322.36
N$1,866.41
List Price:  N$2,613.05
You save:  N$746.63
C$3,683.13
List Price:  C$5,156.53
You save:  C$1,473.40
रु13,265.46
List Price:  रु18,572.18
You save:  रु5,306.71
S/368.80
List Price:  S/516.34
You save:  S/147.53
K382.03
List Price:  K534.86
You save:  K152.83
SAR374.99
List Price:  SAR525.01
You save:  SAR150.01
ZK2,507.99
List Price:  ZK3,511.28
You save:  ZK1,003.29
L456.51
List Price:  L639.13
You save:  L182.62
Kč2,309.58
List Price:  Kč3,233.51
You save:  Kč923.92
Ft36,119.60
List Price:  Ft50,568.89
You save:  Ft14,449.28
SEK kr1,035.66
List Price:  SEK kr1,449.97
You save:  SEK kr414.30
ARS$85,041.86
List Price:  ARS$119,062.01
You save:  ARS$34,020.14
Bs691.17
List Price:  Bs967.67
You save:  Bs276.49
COP$389,392.21
List Price:  COP$545,164.67
You save:  COP$155,772.46
₡50,975.09
List Price:  ₡71,367.17
You save:  ₡20,392.07
L2,469.97
List Price:  L3,458.06
You save:  L988.09
₲730,103.58
List Price:  ₲1,022,174.22
You save:  ₲292,070.64
$U3,841.23
List Price:  $U5,377.88
You save:  $U1,536.64
zł395.04
List Price:  zł553.07
You save:  zł158.03
Already have an account? Log In

Transcript

Hello everyone. This time we will talk about a challenge question which generally people in accounting department faces and often auditors come across this particular problem too. For example, in column B, I have certain check numbers which has been entered by certain staff members. Now, why do I see numbers of varying lengths? Well, mostly the check number is of six digits in length. Now, when they write the zeros zeros, the zeros, which are previous to the numbers, and the press enter in Excel, the zeros go away.

Now, if the data has been retained in such a manner, and eventually you want to apply v lookup to be able to reconcile the balances using check numbers, you would want to add the zeros such that the length is six. And eventually the match takes place so that you can pull numbers using v lookup index etc. Now, the question is, if you are given the data in column A and you have to convert that in column B How do you proceed? If you do it manually? single quotation, double zero, double zero, and then writing 32. Imagine the kind of effort that will be required to do that task.

Well, I'll give you a better technique. If you want physical zeros to be added to the data. First, you need to find out how many zero needs to be added in the first place. Do I need to add a zero in the first check number? No, because it's in total six in length. How many zeros in the next cell?

Well, you need to add two zeros. Now how do I get that information? Well, I'll be using Len formula, length of the total number of characters. And that is going to give me what six in this case and in few cases four, two and three, and so on. And from this formula, what I'm going to add is six minus length. Why?

Because total number of digit is six minus the existing length of characters. So on occasion, you'll get zero Which means no zero needs to be added. And on occasion you will get three which means there are three zeros that needs to be added to this data. Now this is step one, step two, we will use this particular formula as a portion of the reputation formula. So what I'm going to do is I'm temporarily cut this portion of the formula so that it gets on the clipboard. As amendment I require it, I can bring it back.

So now I am proceed with our EBT DPT. Okay, what do you want to repeat? I want to repeat zero. I'm putting that in double quotation. Okay, now it asks me how many times do you want to add the zeros. Now this is tricky.

I cannot write a manual five or manual one or minus zero. I'll have to use that formula is still present on the clipboard. Ctrl V. And I closed one more parenthesis, enter. At this moment, I'm not getting any value. Why? Because there are no zeros needed.

But for the remaining notice been able to repeat the number of zeros that were required in the first place. So the last and final aspect of this trick is, whatever number of zeros you were able to populate using RDP D, you are going to concatenate using the ampersand sign with the original data. And if I do that, it gets combined, so nothing combined with the number and double zero combined with the digit. So there you go. This combo formula will help you add those physical zeros and ones having done so you can very well say copy and then paste special and value. Yeah, there you go.

Number students txt along with the physical zeros. Although let me quickly share one more trick which although not applicable for formula, basic sizes, but it's good enough for printing and display values. If I choose the data, right click, and I go to Format Cells. In the Format Cells as it opens up, I go to custom and in the Type II know those rows zeros Six times zero si press OK, boom, I get the zeros displayed in column B itself. But mind you, this was just a fake zero. As you double click, it still will show you the four digits, not the two zeros.

So if you want to use only for printing purpose and display purpose, use the technique of right click Format Cells. If not, then please go ahead with the combination of formulas that we had used to populate that field as prefix.

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.