Padding cell length with leading zeros

[bernard airlie]

I have a column in a spreadsheet that holds bank account
numbers. Unfortunately, although a bank account number has
to be 8 characters long (e.g. 00012345), some of the
leading zeros have been dropped, meaning that the cell
only contains, for example, 12345.

Some fields only have one leading zero, so the field has
been dropped to 7 characters long, whilst some have no
leading zero so are already the required 8 characters long.

There are 8000 lines on this spreadsheet so changing the
lines manually is not feasible. Any idea how I could
quickly change those fields that are not already 8
characters long? Thanks in advance.

 

[Paul]

I have a column in a spreadsheet that holds bank account
numbers. Unfortunately, although a bank account number has
to be 8 characters long (e.g. 00012345), some of the
leading zeros have been dropped, meaning that the cell
only contains, for example, 12345.

Some fields only have one leading zero, so the field has
been dropped to 7 characters long, whilst some have no
leading zero so are already the required 8 characters long.

There are 8000 lines on this spreadsheet so changing the
lines manually is not feasible. Any idea how I could
quickly change those fields that are not already 8
characters long? Thanks in advance.

Select the entire column and format with the custom format 00000000 .
This means that all the numbers will be shown with eight characters. If you
subsequently enter another number in this column, say 5678, it will also
show as 00005678 . (In other words, no need to type in the leading zeros.)

 

[B Airlie]

Paul

Does this not just change the 'view' of the cell - if I do
this, only the original 5 characters appear in the formula
bar. THe reason why this is important (which I didn't
metion i nhte original post) is that I have to then export
the full 8 characters and import it into our financial
system.

Bernard

 

[Stephen Bullen]

Hi Bernard,

There are 8000 lines on this spreadsheet so changing the
lines manually is not feasible. Any idea how I could
quickly change those fields that are not already 8
characters long? Thanks in advance.

In a column beside it (e.g. in B1), enter the formula:

=TEXT(A1,"00000000")

and copy that down, then copy and paste special>values the result.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk