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 display the original value with four zeroes (0000) instead of six zeroes (000000)
Example: Cell B2 shows 0000008530X4140, so since this cell begins with 000000, then only display 00008530X4140
If text in cell begins with 000001, then display the original value without the preceding five zeroes (00000)
Example: Cell B3 shows 000001566626872, so since this cell begins with 000001, then only display 1566626872.
If text in cell begins with 000005, then display the original value without the preceding five zeroes (00000)
Example: Cell B4 shows 000005353098164, so since this cell begins with 000005, then only display 5353098164.
I appreciate any input, thanks!!
Crossposted.
=IFERROR(--B12&"","0000"&SUBSTITUTE(B12,"000000",""))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))
