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.