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

[devsmom73]

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!

 

[Villalobos]

Hi,

Check this.

 

[r1c1]

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

 

[r1c1]

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

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

 

[devsmom73]

Thank you, all! This was most helpful.

 

[bosco_yip]

Or this in numeric result......

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

Regards
Bosco

 

[devsmom73]

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]

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]

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

 

[PP3321]

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

 

[bosco_yip]

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 :



Regards
Bosco

 

[PP3321]

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