How to fix the length of cells in Excel?

excel

#1

I have different number of characters in cells of column D like below.

Variable                  Length
rest567                        7 
test7890                       8 
variable12                     10              
temp12                          6

Now, I want to populate values in column E against these cell values as the length of cells must be 10 if it is not prefix the required number of zeros as shown below.

Variable                  Length
000rest567                    10 
00test7890                    10 
variable12                    10              
0000temp12                    10

How can we perform this in Excel?

Thanks


#2

Hi,

Use this function -
CONCATENATE(REPT(“0”,10 - LEN(D4)),D4)

This will add leading zeros in front your text and make it’s length to 10.

Hope this helps.

Regards,
Aayush


#3

Hi,

We can use this function also

=TEXT(D4,“0000000000”)

if you want length 11 then just insert one more zero in the quotes.

Hope this helps.

Regards,
Kalyan


#4

Hi @Kalyan,

I think the method suggested by you only works for a numeric string, please try the same on a alphanumeric strings and confirm.

Regards,
Aayush