# Adding zero's to the end of a text string

#### sandy6078

##### New Member
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.

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
L

[No message]

#### Gerald Higgins

##### Well-known Member
Let's say your ID numbers are in col A.
Code:
``=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

##### Board Regular
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 ?

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

#### VoG

##### Legend
Try

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

#### Gerald Higgins

##### Well-known Member
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

##### Board Regular
I like that one Gerald. :wink:

#### sandy6078

##### New Member
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).

Replies
6
Views
272
Replies
2
Views
731
Replies
1
Views
504
Replies
9
Views
502
Replies
2
Views
135

1,191,671
Messages
5,987,957
Members
440,121
Latest member
eravella

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back