Formula to Remove first 4 characters of cell and if parenthesis are present remove them + data inside as well

[duanec]

Receiving data as:

XXXXabc(XXXX)

XXXXabcd

XXXXabcde(XXXXXX)

<tbody>
</tbody>

I need a formula that removes the first 4 characters and removes parenthesis(and data within) IF they appear at the end.

I started with... =MID(A2,5,FIND("(",A2,1)-5) which behaves fine for cells with parenthesis but shows #VALUE ! for cells without parenthesis.

Thoughts?

 

[Eric W]

Welcome to the Board.

Try:

=MID(LEFT(A1,FIND("(",A1&"(")-1),5,99)

 

[JLGWhiz]

Also

=MID(A2,5,(FIND("(",A2))-5)

 

[jtakw]

Hi,

I think JLGWhiz means this:

Book1
	A	B
1	XXXXabc(XXXX)	abc
2	XXXXabcd	abcd
3	XXXXabcde(XXXXXX)	abcde
Sheet96

Cell Formulas
Range	Formula
B1	=MID(A1,5,(FIND("(",A1&"("))-5)

 

[JLGWhiz]

Hi,

I think JLGWhiz means this:

	A	B
1	XXXXabc(XXXX)	abc
2	XXXXabcd	abcd
3	XXXXabcde(XXXXXX)	abcde

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet96​

Worksheet Formulas Cell Formula B1 =MID(A1,5,(FIND("(",A1&"("))-5) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Good catch @jtakw, without the &"(" it would error when no "(" is in the search string.