ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   removing zeroes almost perfect (https://www.excelbanter.com/excel-worksheet-functions/270872-removing-zeroes-almost-perfect.html)

pat67

removing zeroes almost perfect
 
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

David Biddulph

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

Rick Rothstein

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)



All times are GMT +1. The time now is 07:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com