How To Add Leading Zeroes To a Fixed Alphanumeric Length In Excel?

Question

I have the following numbers in Column A and I need to standardize them to a fixed 8 alphanumeric string as in Column B:

    Column A     Column B
1   147731M      0147731M
2   0026146A     0026146A
3   418207L      0418207L
4   0169869M     0160869M
5   2435G        0002435G

I have tried the formula =TEXT(A2,"000000") but it works only when the letters are removed.

Assuming you always have the last non-numeric, try `=CONCATENATE(TEXT(LEFT(A1, LEN(A1)-1), "00000000") ,RIGHT(A1, 1))` — shahkalpesh, Jul 21 '21 at 17:41
`it works only when the letters are removed` - the answer to that is also there, [in a comment](https://stackoverflow.com/questions/3992541/add-leading-zeroes-0s-to-existing-excel-values-to-certain-length#comment18519598_3992588) under the answer. — GSerg, Jul 21 '21 at 17:55
Update: I had to trim the spreadsheet before applying the formula. I tried =CONCATENATE(TEXT(LEFT(A1, LEN(A1)-1), "00000000") ,RIGHT(A1, 1)) and it worked. Thanks! — Anthony Cutajar, Jul 22 '21 at 06:40

score 0 · Answer 1 · answered Jul 21 '21 at 17:42

0

You can use this:

=REPT("0", number_of_zeros-LEN(A1)) & A1
 ^--- repeat "0" number_of_zeros you want - length A1 and then concatenate A1

answered Jul 21 '21 at 17:42

Alberto Sinigaglia

12,097
2
20
48

How To Add Leading Zeroes To a Fixed Alphanumeric Length In Excel?

1 Answers1