Extract Text Between ()

Stuck1 said:

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.

Click to expand...

Where do you need these ID's at?

Hi,

I just want to extrat them into a list, perhaps in a new sheet.

Stuck1 said:

I just want to extrat them into a list, perhaps in a new sheet.

Click to expand...

Give this macro a try...

Thanks so much for the reply and your help. It almost works, however, in some cells there is more than 1 ID. The code only seems to return one ID per cell. Also, there are multiple sheets, is there a way to run the code for the entire workbook?

Thanks again for the help.

Wow Rick

Can you please explain how this code works especially these parts

Does the varray start from first col and row of the used range to to the last col and row?

vArray = WS.UsedRange

List = List & Chr$(1) & Split(Replace(vArray(R, C), ")", "("), "(")(1)
ListParts = Split(Mid(List, 2), Chr$(1))
Range("A1").Resize(UBound(ListParts) + 1) = Application.Transpose(ListParts)
Why is the transposed used here<why is="" the="" transpose="" used<="" html=""></why>

Wow Rick

Can you please explain how this code works especially these parts

Does the varray start from first col and row of the used range to to the last col and row?

vArray = WS.UsedRange

List = List & Chr$(1) & Split(Replace(vArray(R, C), ")", "("), "(") (1)
ListParts = Split(Mid(List, 2), Chr$(1))
Range("A1").Resize(UBound(ListParts) + 1) = Application.Transpose(ListParts)
Why is the transposed used here

Stuck1 said:

Thanks so much for the reply and your help. It almost works, however, in some cells there is more than 1 ID. The code only seems to return one ID per cell. Also, there are multiple sheets, is there a way to run the code for the entire workbook?

Click to expand...

The code I posted already does all of the worksheets in your workbook, as for the multiple ID's in individual cells... you never mentioned that was a possibility, so I did not design the code for it. This new macro should do what you now want...

Hi Rick

Any chance you can have a look at my post and explain how the code works?

That would be appreciated

Sahil123 said:

Wow Rick

Can you please explain how this code works especially these parts

Does the varray start from first col and row of the used range to to the last col and row?

vArray = WS.UsedRange

List = List & Chr$(1) & Split(Replace(vArray(R, C), ")", "("), "(") (1)
ListParts = Split(Mid(List, 2), Chr$(1))
Range("A1").Resize(UBound(ListParts) + 1) = Application.Transpose(ListParts)
Why is the transposed used here

Click to expand...

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