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


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.



Selected Answer

Hi and happy new year!)

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


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



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: 3828) 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: 3828) Jan 10, '20 at 4:42 am
Add to Discussion

I was able to come up with this formula.


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)
    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.


Answer the Question

You must create an account to use the forum. Create an Account or Login