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

[duanec]

Receiving data as:
[TABLE="width: 500"]
<tbody>[TR]
[TD]XXXXabc(XXXX)[/TD]
[/TR]
[TR]
[TD]XXXXabcd[/TD]
[/TR]
[TR]
[TD]XXXXabcde(XXXXXX)[/TD]
[/TR]
</tbody>[/TABLE]

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
XXXXabc(XXXX)	abc
XXXXabcd	abcd
XXXXabcde(XXXXXX)	abcde

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

</tbody>

Sheet96​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=MID(A1,5,(FIND("(",A1&"("))-5)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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