Need a formula that will force a number string to set length and left pad with zeroes

[rjhillcbytwp]

All,
Excel does a lovely job of providing tools to remove leading zeroes, spaces ect. from a string. What I need to do is force a string to a certain length (7 chars), and left pad with zeroes if it is short. I'd like to do this in a formula that I can "drag copy" down the sheet to apply to all rows in a book.

Example: cell contains 1234567 - copy as is to new cell
cell contains 123456 - write 0123456 to new cell
12345 - write 0012345 to new cell
1234 - write 0001234 to new cell
123 - write 0000123 to new cell
I'm simply out of ideas. The reason for the exercise is that I'm doing a vlookup against two sheets, the first sheet is a dump from a database of an unknown type where the cell contains a numeric id. The second is a locked-down, hand-crafted sheet with the comparison ID residing in a text cell. This is the correct sheet, and I cannot change or edit this data. I believe retooling the variable numeric values to a consistent, 7 character string is the way to get my vlookup to find matches.

Any help is always appreciated.

 

[Jonmo1]

Try

=TEXT(A1,"0000000")

 

[VoG]

Try

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

 

[rjhillcbytwp]

You folks are incredible. I can't thank you all enough. Both solutions worked.