Adding zero's to the end of a text string

[sandy6078]

I have a column of vendor id numbers that are text. The id is supposed to be six characters long. I need to add zero's to the end of the text strings that are not six characters long.

 

[Legacy 51064]

[No message]

 

[Gerald Higgins]

Let's say your ID numbers are in col A.

=left(A1&"000000",6)

and copy down as required.
If any of your ID numbers are already more than 6 characters long, this formula will cut them down to the first 6, but there are ways round that if you need it.

 

[Thorin]

Will that work with codes that already 6 digits long, as they will now be 7 digits ?

If they are always 5 or 6 digits long then would this work ?

=IF(LEN(B4)=6,B4,IF(LEN(B4)=5,B4&"0"))

 

[VoG]

Try

=A1&REPT("0",6-LEN(A1))

 

[Gerald Higgins]

Thorin - are you referring to 98illini's solution ? (now deleted).

I think my solution will work for any length of ID code from zero characters to six and more.

 

[Thorin]

I like that one Gerald. :wink:

 

[sandy6078]

The id's are in col A and there are id's that are ids that are the correct length.
This will hopefully be part of a code to clean up a spreadsheet prior to being imported from Access (or exported to Access).