Extract Text Between ()

[Stuck1]

Hi all,

I don't know if anyone can quickly help with this, but is there a way of extracting just the text between two brackets ()?

I have a messy spreadsheet that is largely free text and I only need the ID's that are always between two brackets. There are several sheets to search through, so the soloution will have to search the whole workbook.

Many thanks for any help.

 

[Sahil123]

I'll address the parts of my code by color...

Green: I replace all closing parentheses with opening parentheses so that I wll have a common delimiter for the Split function

Purple: Split creates an array of string values using the opening parentheses as the delimiter.

Orange: The Split function always produces a zero-based array, so we can address the element of that returned arrays directly by placing the element number in parentheses (just like we do with any other array)... the text we want is located after the first delimiter, hence we want element 1 from that zero-based array.

Gray: This just extends the text in the List variable by concatentating the text retrieved by the Split function onto the existing text in the List variable with a character the user cannot type at the keyboard (I use the characteGray: This just extends the text in the List variable by concatentating the text retrieved by the Split function onto the existing text in the List variable with a character the user cannot type at the keyboard (I use the character whose ASCII value is 1, but any character guaranteed not to be in the text concatenated onto List would do).


Blue: Because the first thing concatenated onto List is Chr$(1), it will start the List when List is first empty, so we want to remove it... we do that by retrieving all characters starting with the second character... remember, the first character will always be Chr$(1).


Red: We create an array of names by splitting List using Chr$(1) as the delimiter.


Brown: A one-dimensional array (what Split produces) can be assigned directly to a horizontal range of cells, but since we want a vertical list, we must transpose the values in the array.r

Thank You

These are the 2 bits i am trying to understand. I tried to break it down into mini steps

Orange: The Split function always produces a zero-based array, so we can address the element of that returned arrays directly by placing the element number in parentheses (just like we do with any other array)... the text we want is located after the first delimiter, hence we want element 1 from that zero-based array.

List = Replace(vArray(R, C), ")", "(")
List = Split(List, "(") (1) I understand that the 1 gives the first element since the text should be split like this "(,NAME,(" But why is it that if i remove the (1) Split(LIST, "(") I get an error?

Mid(List, 2), we have omitted the last arguement of the Mid, does that mean it retrieves all the string after the second position?

Brown: A one-dimensional array (what Split produces) can be assigned directly to a horizontal range of cells, but since we want a vertical list, we must transpose the values in the array.

Does the split function store 1d arrays horizontally?

 

[Sahil123]

These are the 2 bits i am trying to understand. I tried to break it down into mini steps

List = Replace(vArray(R, C), ")", "(")

List = Split(List, "(") (1)
I understand that the 1 gives the first element since the text should be split like this "(,NAME,(" But why is it that if i remove the (1) Split(LIST, "(")I get an error?

ListParts = Split(Mid(List, 2), Chr$(1)) we have omitted the last arguement of the Mid, does that mean it retrieves all the string after the second position?

and with this string the list always started with a hidden blank so in the locals window the text is showing like this (Iwill put o to replicate the blank)

oNAME1oNAME2oNAME3 so with the mid starting at the 2nd character should be
NAME1oNAME2oNAME3 now we are splitting by chr1 which is the space (o) so it should now be
NAME1,o,NAME2,o,NAME3 (5 elements with 0 based index) but when i look in locals window is show

listpart(0) = NAME1
listpart(1) = NAME2
listpart(2) = NAME3

My question is what part of the formula removed the SPACE and why did the listpart not show like this if you like

listpart(0) = NAME1
listpart(1) = blank (o)
listpart(2) = NAME2
listpart(3) = blank(o)
listpart(4) = NAME3

Brown: A one-dimensional array (what Split produces) can be assigned directly to a horizontal range of cells, but since we want a vertical list, we must transpose the values in the array.

Does the split function store 1d arrays horizontally?[/QUOTE]

 

[Stuck1]

Amaze-balls! Thanks, Rick.

Sorry I wasn't more specific. Thanks so much, I think this is code I'll try and tweak and use again. Super useful.

 

[Sahil123]

These are the 2 bits i am trying to understand. I tried to break it down into mini steps

List = Replace(vArray(R, C), ")", "(")

List = Split(List, "(") (1)
I understand that the 1 gives the first element since the text should be split like this "(,NAME,(" But why is it that if i remove the (1) Split(LIST, "(")I get an error?

ListParts = Split(Mid(List, 2), Chr$(1)) we have omitted the last arguement of the Mid, does that mean it retrieves all the string after the second position?

and with this string the list always started with a hidden blank so in the locals window the text is showing like this (Iwill put o to replicate the blank)

oNAME1oNAME2oNAME3 so with the mid starting at the 2nd character should be
NAME1oNAME2oNAME3 now we are splitting by chr1 which is the space (o) so it should now be
NAME1,o,NAME2,o,NAME3 (5 elements with 0 based index) but when i look in locals window is show

listpart(0) = NAME1
listpart(1) = NAME2
listpart(2) = NAME3

My question is what part of the formula removed the SPACE and why did the listpart not show like this if you like

listpart(0) = NAME1
listpart(1) = blank (o)
listpart(2) = NAME2
listpart(3) = blank(o)
listpart(4) = NAME3

Brown: A one-dimensional array (what Split produces) can be assigned directly to a horizontal range of cells, but since we want a vertical list, we must transpose the values in the array.

Does the split function store 1d arrays horizontally?

[/QUOTE]


Hope you don't mind me asking you to explain the above. I do appreciate you guys taking time out to help from the bottom of my heart

you guys are awesome

 

[Sahil123]

Hi Rick,

I guess you wont explain any further

Im sorry i ask questions and am not as computer literate like you. I try to learn from you guys and try pick tips from you guys.

Sorry for being a pain and asking questions. Hopefully 1 day i can be like you and then pass on my knowledge to other

Thank you

 

[Rick Rothstein]

These are the 2 bits i am trying to understand. I tried to break it down into mini steps

List = Replace(vArray(R, C), ")", "(")

List = Split(List, "(") (1)
I understand that the 1 gives the first element since the text should be split like this "(,NAME,(" But why is it that if i remove the (1) Split(LIST, "(")I get an error?

Actually, the (1) gives the second element in the array... Split always produces a zero-based array (meaning the first element is numbered 0) and it does so no matter what Option Base setting you have.

ListParts = Split(Mid(List, 2), Chr$(1)) we have omitted the last arguement of the Mid, does that mean it retrieves all the string after the second position?

Yes, unlike the worksheet's MID function, the VBA Mid function's third argument is optional and returns the remainder of the text when omitted.

and with this string the list always started with a hidden blank so in the locals window the text is showing like this (Iwill put o to replicate the blank)

oNAME1oNAME2oNAME3 so with the mid starting at the 2nd character should be
NAME1oNAME2oNAME3 now we are splitting by chr1 which is the space (o) so it should now be
NAME1,o,NAME2,o,NAME3 (5 elements with 0 based index) but when i look in locals window is show

listpart(0) = NAME1
listpart(1) = NAME2
listpart(2) = NAME3

My question is what part of the formula removed the SPACE and why did the listpart not show like this if you like

listpart(0) = NAME1
listpart(1) = blank (o)
listpart(2) = NAME2
listpart(3) = blank(o)
listpart(4) = NAME3

The Split function uses the delimiter to decide where to split the text apart at... the delimiter is never included in any part of the text substring it creates.

Brown: A one-dimensional array (what Split produces) can be assigned directly to a horizontal range of cells, but since we want a vertical list, we must transpose the values in the array.

Does the split function store 1d arrays horizontally?

Split does not store anything... all it does is create a one-dimensional array of text values... Excel assumes one-dimensional arrays are horizontal so that if you assign it to a column of cells, you must transform the array for that assignment.

 

[Sahil123]

Actually, the (1) gives the second element in the array... Split always produces a zero-based array (meaning the first element is numbered 0) and it does so no matter what Option Base setting you have.



Yes, unlike the worksheet's MID function, the VBA Mid function's third argument is optional and returns the remainder of the text when omitted.



The Split function uses the delimiter to decide where to split the text apart at... the delimiter is never included in any part of the text substring it creates.



Split does not store anything... all it does is create a one-dimensional array of text values... Excel assumes one-dimensional arrays are horizontal so that if you assign it to a column of cells, you must transform the array for that assignment.

Oh thank you so much

With this part (Actually, the (1) gives the second element in the array... Split always produces a zero-based array (meaning the first element is numbered 0) and it does so no matter what Option Base setting you have)

is the first delimiter the chr1? And why is it if I were to remove the (1) it produces an error

 

[Rick Rothstein]

Oh thank you so much

With this part (Actually, the (1) gives the second element in the array... Split always produces a zero-based array (meaning the first element is numbered 0) and it does so no matter what Option Base setting you have)

is the first delimiter the chr1? And why is it if I were to remove the (1) it produces an error

If the first character is a delimiter character, then the text in front of it is assigned to element 0... the text in front of the first character is the empty string (""), so element 1 of the array is always the text appearing between the first and second delimiters no matter how many characters there are in front of that first delimiter.

The reason for the error when you remove the (1) is because I declared the List variable you are trying to assign it to as a String... without the (1) the Split function is returning an array, not a String value.

NOTE: If you have any follow-up questions, my answers will be delayed as I am about to go to sleep.

 

[apo]

Post Deleted... I spotted an error..

 

[apo]

ahh.. there was no error.. I just had the column formatted as numbers (which added a comma) in the results..

Here goes again..

I am just learning REGEX (among many other things).. so maybe/most probably there are more efficient ways to do it this way)..

Private Sub CommandButton2_Click()
    Dim objRegex, n
    Set objRegex = CreateObject("vbscript.regexp")
    With objRegex
        .MultiLine = False
        .Global = True
        .IgnoreCase = False
        .Pattern = "[(](\d+)[)]"
        For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
            Set myMatches = .Execute(Cells(i, 1))
            For Each n In myMatches
                If Sheets("Sheet2").Cells(i, 1).Value = "" Then
                    Sheets("Sheet2").Cells(i, 1).Value = Mid(n, 2, Len(n) - 2)
                Else
                    Sheets("Sheet2").Cells(i, 1).Value = Sheets("Sheet2").Cells(i, 1).Value & "," & Mid(n, 2, Len(n) - 2)
                End If
            Next n
        Next i
    End With
End Sub

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:266px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="color:#333333; font-family:Verdana; font-size:10pt; ">xxxx(123)xxxxx(333)xxxx</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="color:#333333; font-family:Verdana; font-size:10pt; ">(12)xxxxx</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="color:#333333; font-family:Verdana; font-size:10pt; ">xxxxxx(2345)</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="color:#333333; font-family:Verdana; font-size:10pt; ">xxxx(123)xxx</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="color:#333333; font-family:Verdana; font-size:10pt; ">(12)xxxxx</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="color:#333333; font-family:Verdana; font-size:10pt; ">xxxxxx(2345)</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="color:#333333; font-family:Verdana; font-size:10pt; ">xxxx(123)xxx(555)</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="color:#333333; font-family:Verdana; font-size:10pt; ">(12)xxxxx</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="color:#333333; font-family:Verdana; font-size:10pt; ">xxxxxx(2345)</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="color:#333333; font-family:Verdana; font-size:10pt; ">xxxx(123)xxx(444)xxx(123)xxx</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:184px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">123,333</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">12</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">2345</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">123</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">12</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">2345</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">123,555</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">12</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">2345</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">123,444,123</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4