Match Number, then Find a string and extract the data between the brackets.

[gsdanger]

Good Day all,

I have a spreadsheet that contains various types of data in it (see attached image).
Each piece of data equates to a number listing (1,2,3,etc...).
In the data area, I want to find the string FS, and extract the contents between the following brackets (xxx).
The number of rows within each number block can vary in numbers, so I guess the Find option will locate the relevant data to be
extracted.
Please help me with this, as I am going crazy trying to figure out how to do this.

King Regards....gsdanger...

 

[alansidman]

Difficult to extract your data from a picture. Would be better if you used XL2BB. In any event

Book4
	F	G	H	I	J
3	NUMBER	DATA		NUMBER	DATA
4	1	xxxxxxxxxxxxxxxxxx		1	-10
5		xxxxxxxxxxxxxxxxxx		2	3.5
6		xxxxxxxxxxxxxxxxxx		3	0
7		XXXXXXFS(-10)		4	12
8	2	xxxxxxxxxxxxxxxxxx
9		xxxxxxxxxxxxxxxxxx
10		xxxxxxxxxxxxxxxxxx
11		xxxxxxxxxxxxxxxxxx
12		xxxxxxxxxxxxxxxxxx
13		XXXXXXFS(3.5)
14	3	xxxxxxxxxxxxxxxxxx
15		xxxxxxxxxxxxxxxxxx
16		XXXXXXFS(0)
17	4	xxxxxxxxxxxxxxxxxx
18		xxxxxxxxxxxxxxxxxx
19		xxxxxxxxxxxxxxxxxx
20		xxxxxxxxxxxxxxxxxx
21		xxxxxxxxxxxxxxxxxx
22		xxxxxxxxxxxxxxxxxx
23		xxxxxxxxxxxxxxxxxx
24		XXXXXXFS( 12)
Sheet1

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"NUMBER"}),
    #"Extracted Text Between Delimiters" = Table.TransformColumns(#"Filled Down", {{"DATA", each Text.BetweenDelimiters(_, "FS(", ")"), type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Extracted Text Between Delimiters", each ([DATA] <> ""))
in
    #"Filtered Rows"

 

[gsdanger]

Hi alansidman,
Thanks for your prompt response.
I tried using XL2BB, however couldn't get it to work properly.
I'm a complete novice using the power query option.
Was looking for a solution using vba.
However, will try to understand the power query option.
Once again, thank you for your solution.

Regards..gsdanger

 

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[Peter_SSs]

I tried using XL2BB, however couldn't get it to work properly.

See if this helps, otherwise please explain just what goes wrong.

Was looking for a solution using vba.

Would you consider a formula solution?

23 09 18.xlsm
	C	D				H	I
1						NUMBER	DATA
2						1	xxxxxxxxxxxxxxxxxx
3							xxxxxxxxxxxxxxxxxx
4							xxxxxxxxxxxxxxxxxx
5							XXXXXXFS(-10)
6						2	xxxxxxxxxxxxxxxxxx
7							xxxxxxxxxxxxxxxxxx
8							xxxxxxxxxxxxxxxxxx
9							xxxxxxxxxxxxxxxxxx
10							xxxxxxxxxxxxxxxxxx
11	1	-10					XXXXXXFS(3.5)
12	2	3.5				3	xxxxxxxxxxxxxxxxxx
13	3	0					xxxxxxxxxxxxxxxxxx
14	4	12					XXXXXXFS(0)
15						4	xxxxxxxxxxxxxxxxxx
16							xxxxxxxxxxxxxxxxxx
17							xxxxxxxxxxxxxxxxxx
18							xxxxxxxxxxxxxxxxxx
19							xxxxxxxxxxxxxxxxxx
20							xxxxxxxxxxxxxxxxxx
21							xxxxxxxxxxxxxxxxxx
22							XXXXXXFS( 12)
Extract values

Cell Formulas
Range	Formula
D11:D14	D11	=RIGHT(SUBSTITUTE(SUBSTITUTE(INDEX(INDEX(I:I,MATCH(C11,H:H,0)):I$22,MATCH("*FS(*",INDEX(I:I,MATCH(C11,H:H,0)):I$22,0)),"(",REPT(" ",20)),")",""),20)+0

 

[gsdanger]

To alansidman and Peter SSs,
Thank you both for your responses.
alansidman,
I have looked at the links you supplied regarding the Power Query option, and it looks pretty good. I will educate myself in regards to utilising this option.

Peter SSs,
Thanks for your formula solution. works fine...Thanks.
I'm having trouble understanding the formula content. I will disect each formula input, to try and understand how the formula actually works.
I would never have thought of using the substitute option!
However, it works fine and once again, thank you.

Kind Regards...gsdanger

 

[gsdanger]

Calling Peter_SSs...
Hi Peter_SSs,
Further to the solution you gave me a few weeks ago.
The solution works fine. Thanks for that.
However, there are a few modifications to the original example that renders your solution as not working.
Everything is the same, except for the following. The data in column I extends for 2000 rows, and the string I want to extract is NOT to the right of the data, as after the string I want to extract, there is more data, so I guess the Right function is irrelevant. Maybe the MID function can be used?
Can you please see if you can assist me with this modification?

Kind Regards...GSDANGER.

 

[Peter_SSs]

What about some sample data and the expected results with XL2BB. Remember ..

See if this helps, otherwise please explain just what goes wrong.