# "(" is being treated as a simple ( within Excel formula

0

The "(" in the FIND function is being treated simply as ( in the following formula (this creating an invalid formula):

=IF(AND(MID(A1,FIND("(",A1)-1,1)>="0",MID(A1,FIND("(",A1)-1,1)<="9"),MID(A1,FIND("(",A1)-4,4),IF(AND(MID(A1,FIND("(",A1)-2,1)>="0",MID(A1,FIND("(",A1)-2,1)<="9"),MID(A1,FIND("(",A1)-5,4), IF(AND(MID(A1,FIND("(",A1)-3,1)>="0",MID(A1,FIND("(",A1)-3,1)<="9"),MID(A1,FIND("(",A1)-6,4),)))

I have tried two sets of quotes ""("" - no help.  Extra spaces on each side of the  "("  - no help.  Using SEARCH instead of FIND causes the same problem. The simple formula of FIND("(",A1) works fine, as expected.  When embedded inside IF AND and MID, it is failing.

0

Hi and happy new year!)

You can find that by making the FIND function like this:

``=FIND(CHAR(34)&"("&CHAR(34),A1)``

CHAR(34) is another way to represent the double quotation marks.

### Discussion

Why not simply this? Find(Char(40),A1)
The formula suggested in the answer will find "(" - 3 characters but not the opening bracket in "my text (sample)". Char(40) can be used to directly replace "(" in your own formula.
Note, however, that =FIND("(",A1) and =FIND(CHAR(40),A1) will both return a #VALUE error if there is no "(" in A1. Your formula doesn't appear to make a provision for that.
Apart from that, your formula looks terribly complicated. There might be a simpler approach. Do you care to disclose what's in A1 and what your formula aims to achieve?
Variatus (rep: 3838) Jan 9, '20 at 4:44 am
Excellent!  Thank you Don for the concept and Variatus for the correct solution.  I should have thought of replacing "(" with CHAR(40).  I am really amazed that Excel cannot handle the "(".  There is always a left parenthesis in the cells so I did not provide for it not being there.  The formula extracts a four digit number that is either 0, 1 or 2 spaces in front of the first left parenthesis in the cell.  I would not be surprised if there is a simpler approach but this works.
jlaindy (rep: 2) Jan 9, '20 at 8:42 am
If you prefer Variatus' solution, make sure to remove mine as the selected answer and select his as the answer.
don (rep: 1825) Jan 10, '20 at 2:41 am
Thank you, Don. jlaindy has correctly identified that you provided the concept. Given that he could have found the solution himself. Kudos to you.
Variatus (rep: 3838) Jan 10, '20 at 4:42 am
0

I was able to come up with this formula.

``=MID(MID(A2,FIND(CHAR(40),A2)-6,6),MIN(FIND({0,1,2,3,4,5,6,7,8,9},B2&"0123456789")),4)``

It looks for the first number in MID(A2,FIND(CHAR(40),A2)-6,6), based on the assumption that there will only be 4 numerics in the six characters before the left parenthesis, and then takes 4 characters from that point. It will fail if there are less than 6 characters before "(" or if the 4-digit number you want is part of a larger number.

Your idea to look from the right is better. In Excel 2016 there is a function to reverse a string. My efforts to do it with an array formula failed. Therefore I wrote a UDF (User Defined Function).

``````Function ExtractID(Cell As Range) As Variant
' Variatus @TeachExcel
' 10 Jan 2020

Dim Fun As Variant

Fun = Cell.Value
Fun = Mid(Fun, InStr(Fun, Chr(40)) - 6, 6)
Do While Len(Fun) And (Not IsNumeric(Right(Fun, 1)))
Fun = Left(Fun, Len(Fun) - 1)
Loop
ExtractID = Right(Fun, 4)
End Function``````

You will find it deployed in the sheet Solution 2 of the attached workbook. The obvious advantage of a UDF is that you can tweak it for lots of eventualities. The above code is rudimetary.

On the tab Solution 1 of the attached workbook you will find a breakdown of the worksheet function. The key element it introduces to our discussion is the function to look for a number in a string. This I am indebted for to this site where it is further explained. I wanted to deploy it in a reversed string, looking for the first number to the left of "(". Perhaps you can succeed where I failed. Or you might use it to find the number you want from the right, forgetting "(" as a point of reference entirely.