Forum Discussion
Excel formula to remove a certain number of characters IF...
I see two ways (and there are probably more) to approach this.
If those are the only two kinds of conditions, this formula works:
=IFS(
LEFT(B2,6)="000000",B2,
LEFT(B2,5)="00000",RIGHT(B2,LEN(B2)-5)
)
If you've got a variety of leading strings to be dealt with, and they'd always be in the first column (as in your illustration), then this formula
=IF(
LEFT(B2,6)="000000",B2,
IF(LEFT(B2,6)=A2,RIGHT(B2,LEN(B2)-5)
)
)
A spreadsheet is attached showing both approaches.
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))
