removing zeroes almost perfect
On 26/07/2011 20:55, pat67 wrote:
Hi I am using this to convert BHQ0009540 to C9540
=CONCATENATE("C",MID(RIGHT(B2,7),SEARCH(LEFT(SUBST ITUTE(RIGHT(B2,7),"0",""),
1),RIGHT(B2,7)),25))
the issue is sometimes i need a leading 0 like converting LPB0000385
to C0385. using the above I get C385.
I am at a loss. any ideas?
Thanks
="C"&TEXT(RIGHT(B2,7),"0000") if this suits the input format.
--
David Biddulph
|