Pad text to equal length In Excel 1

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.

Find Cube Root of number in Excel

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.

Pad text to equal length In Excel 2

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:

Pad text to equal length In Excel 3

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).

Pad text to equal length In Excel 4

Then we will put an & sign and input the REPT Function which is as follows:

=REPT(

The following formula requires text, number times.

Pad text to equal length In Excel 5

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.

Pad text to equal length In Excel 6

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.

Pad text to equal length In Excel 7

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:

=LEN(

The following formula requires text.

Pad text to equal length In Excel 8

The text will be the data in hand i.e. Black (cell B3).

Pad text to equal length In Excel 9

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:

=B3&REPT(“*”,20-LEN(B3))

Pad text to equal length In Excel 10

And there you go; the result came out as Black***************

Pad text to equal length In Excel 11

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.

Pad text to equal length In Excel 12

And BAAAAMMMM!!! There you go. As you can see in the image above, all the four colours are of equal length now.

Pad text to equal length In Excel 13

So, it’s easy, isn’t it?