Forum Discussion
Excel formula to remove a certain number of characters IF...
Hello All, I'm looking for a formula that will see if a cell begins with either "000000", "000001", or "000005", and if so, modify the text like below. If text in cell begins with 000000, then ...
John, i think you missed the request that if there are 6 0s to reduce it to 4 0s. Here is an alternate formula:
=MID(B2, IF(LEFT(B2,6)="000000",3, IF(LEFT(B2,5)="00000",6,1)), LEN(B2))
OR
=MID(B2, IF(--LEFT(B2,6), IF(--LEFT(B2,5),1,6), 3), LEN(B2))
oops and I missed the possibility of 00000x where x is NOT 1 or 5 so in that case:
=MID(B2, IF(--LEFT(B2,6), IF((--LEFT(B2,6)=1)+(--LEFT(B2,6)=5),6,1)), 3), LEN(B2))
