# 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

• 8.9 KB Views: 4

Hi,

Check this.

#### Attachments

• 9.5 KB Views: 19

#### r1c1

Staff member
@Villalobos
brilliant use of array find with {-1,1} multiplication..

#### r1c1

Staff member
@devsmom73 As you say you need the 4 digits inside parenthesis, you can use this too.

=MID(A4,SEARCH("(*)",A4)+1,4)

#### devsmom73

##### New Member
Thank you, all! This was most helpful.

#### bosco_yip

##### Excel Ninja
Or this in numeric result......

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

Regards
Bosco

#### devsmom73

##### New Member
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!

#### bosco_yip

##### Excel Ninja
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

#### devsmom73

##### New Member
Great explanation! Now I understand. Wasn't thinking about the () being read as a negative number. Thanks again!

#### PP3321

##### Active Member
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.

#### bosco_yip

##### Excel Ninja
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.
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 :

Regards
Bosco

#### PP3321

##### Active Member
Thank you so much @bosco_yip !!!
I did not see the first open-parethesis.
This is super helpful!!! Thank you!!!