removing zeroes almost perfect
="C"&TEXT(RIGHT(B2,7),"0000") if this suits the input format.
If you do that formula this way instead...
then you will be able to copy it down through Column B cells that are blank
and not end up printing out that lone "C" character.
Rick Rothstein (MVP - Excel)