Padding cell length with leading zeros

  • Thread starter Thread starter bernard airlie
  • Start date Start date
B

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.
 
bernard airlie said:
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.
Click to expand...

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.)
 
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
 
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.
Click to expand...

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
 
Back
Top