Excel 2007 : Need help with a concatenate issue

Happy Holidays to all.
Thanks in advance to all that help.

I have a list of equipment serial numbers that are in a different format than our main list. See sample of sheet attached. I did a search and replace for the year to convert to a the last two digits. I did a custom number format on the serial number column to make those that are 4 digits to five digits.The first issue is when I do the concatenate I lose the zeros that formatted into the numbers. The second is how do I get the dashes in the correct location.

Thanks to all that help.

I am puzzled because there is no sign of a concatenation in your spreadsheet.

Maybe this does it for you:

Try this

=B3&"-"&RIGHT(C3,2)&"-"&TRIM(D3)&TEXT(E3,"00000")

Or
=CONCATENATE(B3,"-",RIGHT(C3,2),"-",TRIM(D3),TEXT(E3,"00000"))

Both do the same thing.

Hope this helps