Excel formula to remove a certain number of characters IF...

[Jfreshh330]

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!!

 

[Fluff]

How about

++Fluff.xlsm
	A	B	C
1
2		0000008530X4140	00008530X4140
3		000001566626872	1566626872
4		000005353098164	5353098164
Data

Cell Formulas
Range	Formula
C2:C4	C2	=IF(LEFT(B2,6)="000000",MID(B2,3,LEN(B2)),IF(OR(LEFT(B2,6)={"000001","000005"}),MID(B2,6,LEN(B2)),B2))

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Excel formula to remove a certain number of characters IF...
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.