Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how many perfect score | Excel Discussion (Misc queries) | |||
How to fit each cell into a perfect square? | Excel Discussion (Misc queries) | |||
Making the perfect sort. | Excel Discussion (Misc queries) | |||
searching for my perfect algorithm.... | Excel Discussion (Misc queries) | |||
looking for my perfect algorithm | Excel Discussion (Misc queries) |