Pad text to equal length: When we are dealing with loads of cells which have data filled in it, we observe that due to the length of different data’s (or characters together) they seem very inappropriate.
As you can see in the image above, all texts are not of equal length, this is obvious due to the no. of characters it holds but the text filled in those cells at an equal length we use this method.
Let’s begin with the example, as you can see in the image above, we have taken 4 colors – Black, Yellow, Purple and Pink.
As you can see that the 4 colors are not of the same length so to bring them into equal length, here’s what you can do:
As every formula begins with an equal to sign, so let’s input that first. Then we will select the data in hand, i.e. Black (cell B3).
Then we will put an & sign and input the REPT Function which is as follows:
The following formula requires text, number times.
In text, we will input the following:
There is a reason behind it, first we added the quotation marks, this is because we are adding a specification in excel. Second, we inputted the asterisk in between the quotation marks, this is because we want to cover the space through asterisk. If you want to use anything else like a number, another symbol, or an alphabet, you can input the same between the quotation marks.
But please note, do not forget to put the quotation marks or the whole formula will crash.
After text, we will move to the number times, here, you need to input the number of texts you want in your data.
Say 20, as shown in the image above, this means that the total characters in the cell will be 20 in number including the data already present in it.
So, if Black is of 5 characters, then there will be 15
asterisks after it.
After inputting the number times, we will subtract the same with LEN Function, this is being done because as we explained above that those 20 number will also include the data present in the cell, so to adjust the same we need to use the help of the LEN Function.
The LEN Function is as follows:
The following formula requires text.
The text will be the data in hand i.e. Black (cell B3).
After selling the text of the LEN Formula, we will close the same along with the REPT Formula.
So overall, our formula becomes as follows:
And there you go; the result came out as Black***************
Now all you have to do is drag down the formula to save yourself time. (just like it is shown in the image above)
Please note: To drag down a formula for the usage of other cells, you need to put your cursor at the bottom right corner of the formula cell, there you will notice a bold plus sign, now all you have to do is drag it down till its required.
And BAAAAMMMM!!! There you go. As you can see in the image above, all the four colours are of equal length now.
So, it’s easy, isn’t it?