Concatenating leading zeroes and VLOOKUP

[Vullishoop]

Hi all, I've got a problem that feels like it should be simple to rectify but I can't for the life of me think how to do it.

I have a list of item codes ranging between 1 and 9999. I am trying to use VLOOKUP to get data from spreadsheet A and populate fields in spreadsheet B. In spreadsheet A the item codes do not having lead zeroes (e.g. 1234) but in spreadsheet B they do have leading zeroes (e.g. 01234).

I have used Concatenate in Spreadsheet A to add a zero to the start of each item code, and this works fine for numbers between 1000 and 9999.

However this means that item codes from 1 to 999 do not have the correct number of zeroes. Using CONCATENATE("0",A1) will for example return 03456 and 07890 correctly, but it returns 01, 023, 0345 for instance when dealing with shorter strings.

Is there a formula that I can use that will change the data in spreadsheet A into a five-digit number, adding leading zeroes as appropriate?

Thanks for reading.

[Andrew-R]

=text(a1,"00000")

[Vullishoop]

Excellent, thank you very much for the quick reply!