exact length of the cell

[sabi0398]

I need to add a leading 0 if the length of the text is less than 10 char
Example: I have in A1 34126 so I need to 5 leading 0s in front
or
96750004 -so I need to add 2 leading 0s in front
What is the way to do it?

 

[T. Valko]

Use a custom number format of: 0000000000

Format>Cells>Number tab>Custom
Below "Type:" enter 10 zeros
OK

 

[Gary''s Student]

Format > Cells... > Custom > 0000000000

 

[Bernie Deitrick]

sabi,

Note that formatting the cells will not change the actual length of the string, just how it is
displayed.

Use = TEXT(A1,"0000000000")

if you need the actual length to be 10 - copy the formula and paste down to match your data. You
can also copy those formula and paste values over the original data if that is required.

HTH,
Bernie
MS Excel MVP

 

[muddan madhu]

in B1 put this formula

=LEFT(A1*1&TEXT(R19,"0000000000"),10)

 

[muddan madhu]

sorry

=Text(A1,"0000000000")

in B1 put this formula

=LEFT(A1*1&TEXT(R19,"0000000000"),10)