Ensure that all numbers are 8 digits long

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DianeG - 26 Mar 2008 12:05 GMT

Hi

A client of mine imports data into Excel 2003 from an external system. The
numbers vary in length when imported, never more than 6 characters long. The
client always need the numbers to be 8 characters, is there a way to add
zeros to the start of the number to make it 8 characters in length what ever
the original length was?

Thanks in advance

Diane

Reply to this Message

Mike H - 26 Mar 2008 12:10 GMT

Possibly a custom format of

00000000

Mike

> Hi
>
[quoted text clipped - 7 lines]
>
> Diane

Reply to this Message

Pete_UK - 26 Mar 2008 12:13 GMT

You could put this in an adjacent column:

=TEXT(A1,"00000000")

and copy down, assuming your numbers are in column A. This will
convert the numbers to text values, but would make them all 8 digits
in length with appropriate number of leading zeros. You could fix the
values and then copy these over the values in column A.

Hope this helps.

Pete

> Hi
>
[quoted text clipped - 7 lines]
>
> Diane

Reply to this Message

DianeG - 26 Mar 2008 12:55 GMT

Thanks very much Pete and Mike for your responses. Both work perfectly!

> You could put this in an adjacent column:
>
[quoted text clipped - 20 lines]
> >
> > Diane

Reply to this Message

Pete_UK - 26 Mar 2008 14:18 GMT

You're welcome, Diane - thanks for feeding back.

Pete

> Thanks very much Pete and Mike for your responses. Both work perfectly!
>
[quoted text clipped - 22 lines]
>
> > > Diane

Reply to this Message