Extract text after some common specific string

[wongsiuon]

HI
i want a formula. see attached
i want to extract text after some common specific string.

i want to return the quantites and the text after some common specific string. thanks a lot.

[Pepe Le Mokko]

Clear as mud. Please complete your example. Where is your source data?

[AliGW]

I don't disagree that this is unclear, but I believe the source data is the data in A1 and the result data is the list below it. Dreadful data gathering scheme!!!

[wongsiuon]

Hi Pepe and Ali,
The data source is a long text in cell A1. And want to extract the text which shows in the desired results.
I did one formula by myself but it is not working. And it is difficult to extract the text as desired results.
Thanks a lot.

[Glenn Kennedy]

I don't see any logical pattern here. So I can't see how to get to your desired result.

[Bosco]

Formula solution for Excel 2013 or above for the using of FILTERXML function

1] In order to avoid a long repeated split data formula, try to create a define name.

Select D4 >> Define Name >>

>> Name : SplitSpecData

>> Refers to : =FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(A$1,$E4,"<r/>")," ","</b><b>")&"</b></a>","//b[r]")

>> OK

Then,

2] In D4, formula copied down :

=IFERROR(IF(1-ISERR(FIND(")",SplitSpecData)),MID(SplitSpecData,FIND(")",SplitSpecData)+1,99),SplitSpecData),E4)

3] In C4, formula copied down :

=IF(D4="","",LEFT(A$1,FIND(" ",A$1)-1))

Regards
Bosco

[wongsiuon]

WOW, Bosco, you are genius.. it works...thanks a lot.