VBA: Count characters between parentheses in cell

[yessymtz9]

Hello ,

I'm trying to count the number of character between parentheses in excel.
I already have a code that count the number of characters on a cell, but then I have cells with boolean operators with parentheses "()&()".
For example:

Code Configurable item Value Comment
GY Color 0 0 is for gray
WH Color 1 1 is for white
LT Weight 0,0,1 0x1 light weight
HY Weight 0,1,0 0x2 heavy weight
(GY)&(LT) Material 0,0,0,1 0x1 Al
(WH)&((LT)|(HY)) Material 0,1,0,0 0x4 Ag
((WH)|(GY))&(HY) Material 1,0,0,0 0x8 Au


My current macro count the number of characters (must be 2 on column "code" to be ok), and if it is more or less than 2 it fills the cell with red.
I can't make the code to count inside the "(" ")", currently I'm ignoring the cells with parentheses.

I found a formula: =Mid(A1, Find("(", A1)+1, Find(")",A1)-Find("(",A1)-1)
But as I have several parentheses it does not work and also I'm trying to add it to my current macro.

I'm thinking of using string variables to save the values for example in ((WH)|(GY))&(HY):
Val1= WH
Val2 = GY
Val3 = HY

Any idea how I can do it?

[jindon]

What do you really want to do?

Just count the characters or extract the characters?

[yessymtz9]

Count the characters between each parentheses. For example:

((WH)|(GY))&(HY)

The first "string" to count would be "WH", then "GY" and then "HY"

[jindon]

I don't think it is clear.
Is it total of 6 or 2,2,2 ?

Upload a workbook with data and the result that you want to avoid misunderstanding.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[yessymtz9]

SOLVED:

Please Login or Register  to view this content.