May 20 2022 11:56 AM
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!!
May 20 2022 12:36 PM
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.
May 20 2022 01:01 PM - edited May 20 2022 01:10 PM
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))
May 20 2022 01:07 PM
John, i think you missed the request that if there are 6 0s to reduce it to 4 0s.
You're right. An adjustment would be needed, but the idea is there ... and you've helped with still more.
Thanks!
May 20 2022 01:50 PM
Crossposted.
=IFERROR(--B12&"","0000"&SUBSTITUTE(B12,"000000",""))