• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula to pull data that is within parenthesis in a field?

devsmom73

New Member
Is there a formula that can be used to extract data that is in parenthesis in a field? I can use the =Left but the data is not always in the same position in each field in the worksheet. On the attached file I have given four examples of what might appear in my spreadsheet. I only need the 4 digits within the () in Column A to show in Column B.

Thank you!
 

Attachments

  • Book3.xlsx
    8.9 KB · Views: 4
Or this in numeric result......

=-MID(A4,FIND("(",A4),6)

Regards
Bosco

This is interesting. I've never seen the MID formula used with a negative (-). Actually can't seem to find anything on the web for it. I like it. But I'm not sure I understand it. LOL! Thanks for your help!
 
This is interesting. I've never seen the MID formula used with a negative (-). Actually can't seem to find anything on the web for it. I like it. But I'm not sure I understand it. LOL! Thanks for your help!

The MID formula used with a negative explanation.

For example, after the formula extraction

=-(3759)

Since Excel treat (3759) equal to -3759

=--3759

=3759

Regards
 
Great explanation! Now I understand. Wasn't thinking about the () being read as a negative number. Thanks again!
 
Thank you for such wonderful thread. I learned so many things.
If you do not mind, could you allow me to ask 1 question to @Villalobos...?

This is original formula
=MID(A4,FIND("(",A4&"(")+1,SUM(FIND({"(";")"},A4&"(0)")*{-1;1})-1)

When I removed &"(0)"
It worked OK too.

=MID(A4,FIND("(",A4&"(")+1,SUM(FIND({"(";")"},A4)*{-1;1})-1)

Could you please explain to me the meaning of &"(0)"...?

Your amazing formula is helpful to me because in my situation the number of digits vary from cell to cell.

Thanking you in advance...
 
Thank you for such wonderful thread. I learned so many things.
If you do not mind, could you allow me to ask 1 question to @Villalobos...?
This is original formula
=MID(A4,FIND("(",A4&"(")+1,SUM(FIND({"(";")"},A4&"(0)")*{-1;1})-1)
When I removed &"(0)"
It worked OK too.
=MID(A4,FIND("(",A4&"(")+1,SUM(FIND({"(";")"},A4)*{-1;1})-1)
Could you please explain to me the meaning of &"(0)"...?
Your amazing formula is helpful to me because in my situation the number of digits vary from cell to cell.
Thanking you in advance...

Formula [1] : =MID(A4,FIND("(",A4&"(")+1,SUM(FIND({"(";")"},A4&"(0)")*{-1;1})-1)

Formula [2] : =MID(A4,FIND("(",A4)+1,SUM(FIND({"(";")"},A4)*{-1;1})-1)

In formula [1] this part : &"(" and &"(0)" used for error control and return blank while the searching criteria is not found.

For example :

upload_2016-6-25_12-51-3.png

Regards
Bosco
 
Back
Top