Horizontal cycle (VBA)

[Fixed]

Hello!

I have some macro:

Sub bracketsSub()
    Dim arr()   As Variant
    Dim x       As Long
    
    x = Cells(Rows.Count, 1).End(xlUp).Row
    arr = Cells(1, 1).Resize(x, 2).Value
    
    For x = LBound(arr, 1) To UBound(arr, 1)
        arr(x, 2) = brackets(CStr(arr(x, 1)))
    Next x
    Cells(1, 1).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
    Erase arr
End Sub
Private Function brackets(ByRef sss As String) As String
Dim aaa As Long '---
Dim bbb As Long '---
On Error GoTo Error_handler:
     aaa = InStr(sss, "(")
     bbb = InStr(aaa, sss, ")")
     brackets = Mid(sss, aaa + 1, bbb - aaa - 1)
Exit Function
Error_handler:
brackets = "-no-"
End Function

It's extract some text (first found text) from the brackets "()" from the strings in the "A" column and copy to "B" column.
What I should do to extract all texts from the brackets?

Example I want to get:

	A	B	C	D
1	(yes)(no)(don't know)<strike></strike>	yes	no	don't know<strike></strike>
2	(yes)(no)<strike></strike>	yes	no	-no-
3	(yes)<strike></strike>	yes	-no-<strike></strike>	-no-<strike></strike>

<tbody>
</tbody>

Help me please!

 

[Fixed]

This works for both, but it will be limited depending how far you move the goal posts.!!!

[COLOR=navy]Sub[/COLOR] MG27Oct53
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, Txt [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Sp [COLOR=navy]As[/COLOR] Variant, Mch
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
Mch = Array("yes", "no", "don'[COLOR=green][B]t know")[/B][/COLOR]
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    Dn.Offset(, 1).Resize(, 3) = "-no-"
        [COLOR=navy]For[/COLOR] n = 0 To 2
            [COLOR=navy]If[/COLOR] InStr(Dn.Value, Mch(n)) > 0 [COLOR=navy]Then[/COLOR]
                Dn.Offset(, n + 1) = Mch(n)
            [COLOR=navy]End[/COLOR] If
        [COLOR=navy]Next[/COLOR] n
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Thanx Mick, but in brackets can be any words (symbols).

 

[Rick Rothstein]

Thanx Rick, I've used your updated macro:

...and it don't work as I showed in sample from the #5 .

Sorry, I missed that the last example value did not have parentheses around it. Give this modified macro a try instead...

[table="width: 500"]
[tr]
	[td]Sub SplitOutOfParentheses()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("B1:B" & LastRow) = Evaluate(Replace("IFERROR(IF(A1:A#="""","""",SUBSTITUTE(MID(A1:A#,2,LEN(A1:A#)-2),MID(LEFT(A1:A#&""("",FIND(""("",A1:A#&""("",2)),FIND("")"",A1:A#),200),""("")),A1:A#)", "#", LastRow))
  Columns("B").TextToColumns , xlDelimited, , , False, False, False, False, True, "("
End Sub[/td]
[/tr]
[/table]

By the way, in the future when you get a response that does not work correctly, don't just say "it didn't work"... please tell us in what way it did not work (what happened that shouldn't have happened or, conversely, what did not happen that should have)... this will make it easier for us to track down where the problem is instead of our having to try and figure it out on our own.

 

[Fixed]

Sorry, I missed that the last example value did not have parentheses around it. Give this modified macro a try instead...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub SplitOutOfParentheses()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("B1:B" & LastRow) = Evaluate(Replace("IFERROR(IF(A1:A#="""","""",SUBSTITUTE(MID(A1:A#,2,LEN(A1:A#)-2),MID(LEFT(A1:A#&""("",FIND(""("",A1:A#&""("",2)),FIND("")"",A1:A#),200),""("")),A1:A#)", "#", LastRow))
  Columns("B").TextToColumns , xlDelimited, , , False, False, False, False, True, "("
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

By the way, in the future when you get a response that does not work correctly, don't just say "it didn't work"... please tell us in what way it did not work (what happened that shouldn't have happened or, conversely, what did not happen that should have)... this will make it easier for us to track down where the problem is instead of our having to try and figure it out on our own.

OK, Rick, I will try.

Results of the SplitOutOfParentheses:

A	B	C	D
(yes)---(no)--(I don't know)--some text<strike></strike>	yes	no)--<strike></strike>	I don't know)--some tex<strike></strike>
(yes)--(no)---some text<strike></strike>	yes<strike></strike>	no)---some tex<strike></strike><strike></strike>
some text<strike></strike>	#REF!<strike></strike>

<tbody>
</tbody>

Example from the post #5 :

	A	B	C	D
1	(yes)---(no)--(I don't know)--some text	yes	no	I don't know<strike></strike>
2	(yes)--(no)---some text	yes	no	-no-
3	some text	-no-	-no-<strike></strike>	-no-

<tbody>
</tbody>

 

[Rick Rothstein]

Example from the post #5 :

	A	B	C	D
1	(yes)---(no)--(I don't know)--some text	yes	no	I don't know<strike></strike>
2	(yes)--(no)---some text	yes	no	-no-
3	some text	-no-	-no-<strike></strike>	-no-

<tbody>
</tbody>

Is what I highlighted in red actually what you want from the split out? If not, show me what you do want.

 

[Fixed]

There are can be <= 10 000 symbols in one cell and number of the text parts in the brackets <= 100.
There are can be any text in the brackets - not only "yes", "no", "I don't know".
There are can be any words around the brackets.

 

[Fixed]

Yes, Rick.

 

[Rick Rothstein]

Yes, Rick.

So you want -no- returned for text not in parentheses, correct?

If so, why in the last example where there is only one piece of text is -no- returned three times? What is the rule for how many times to repeat it?

 

[Fixed]

So you want -no- returned for text not in parentheses, correct?

If so, why in the last example where there is only one piece of text is -no- returned three times? What is the rule for how many times to repeat it?

This is the method to fill the empty cells in those columns that not empty.

 

[Rick Rothstein]

This is the method to fill the empty cells in those columns that not empty.

Okay, next question... is the only thing the can come between a closing parenthesis ()) and an opening parenthesis (() either nothing or two dashes like all your examples have shown so far? If not, then what can be there... any characters? If so, should they become -no- in the output like the text at the end in your examples?

 

[Fixed]

I wonder if there will be that cases, but if we find it I think we should grab only the text w/o the round brackets "(" text without the round brackets inside ")".

A	B
(some text(another text))	another text

<tbody>
</tbody>