View Single Post
  #3   Report Post  
Old July 27th 11, 03:16 AM posted to microsoft.public.excel.worksheet.functions
Rick Rothstein Rick Rothstein is offline
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2008
Posts: 5,934
Default removing zeroes almost perfect

="C"&TEXT(RIGHT(B2,7),"0000") if this suits the input format.

If you do that formula this way instead...

=TEXT(RIGHT(B2,7),"C0000")

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)