Pre-pend required number of characters in Excel

Question

I need to prepend field values in an Excel sheet with the required number of characters to equal 5 characters in the field, then concatenate two fields and have all of the characters show in the new field.

Example:

Field 1 | Field 2 | Show as

abc | 123 | 00abc00123

d | 5678 | 0000d05675

Ideas?

Answer 1

I think what you may want is something like:

=REPT(0,5-LEN(A1))&A1&REPT(0,5-LEN(B1))&B1

Answer 2

You could also use TEXT on a number field.

=REPT(0,5-LEN(A1))&A1&TEXT(B1,"00000")