Search a specific character if meet look for another then extract data

[seolann]

Hi

I have a field with the similar data like following.

{\rtf1\deff0{\fonttbl{\f0 Courier New;}}\sectd\pard\plain\ql{\cf0 FUTEK F3000 SIDM Printer }\par\pard\plain\ql{\cf0 WITH 6 YEARS WARRANTY FOR PRINTER}\par }

I need a formula to search if the field have the specify character 'warranty' then look for position of '{' and '}' within the block to extra the data '\cf0 WITH 6 YEARS WARRANTY FOR PRINTER' to another cell.

The position maybe different for rows.

Any help is greatly appreciated.

[bmouse]

Does the text fragment \cf0 WITH 6 YEARS WARRANTY FOR PRINTER is always the same length? If so, I believe this solution might work.

[seolann]

No all rows have the same length and the position maybe different.

[bmouse]

I understood the position of the fragment may be different, what I asked was is the length of the fragment itself always the same? Is the fragment always in this format \cf0 WITH 6 YEARS WARRANTY FOR PRINTER? And not this \cf0 6 YEARS WARRANTY FOR PRINTER? Or this \cf0 WITH 6 YEARS WARRANTY?

If the fragment is always the same length, it doesn't matter where it is located. My formula finds the end phrase ("PRINTER}"), then substracts the necessary number of characters (which is 31 in this case) from the position and returns the whole phrase. For this approach, the position of the text fragment is irrelevant, what matters is that it always has the same amount of characters.

[seolann]

Hi Bmouse,

Thank you for the formula. I have attached mydata with the formula, it seem not applicable for all.

[bmouse]

Ok, so the fragment itself differs in length, which means a different solution is necessary.

[seolann]

Actually the formula should search 'warranty' from the long text. Then go front and look for { sign and behind for } sign. Extract data start with { with the specify character 'warranty' in the block. End when the } sign then.

[seolann]

Hi

can the excel VBA handling the datas?

[bmouse]

You can search for the word "WARRANTY" and get the position number, then use that number to find the "}" sign at the end of the phrase. What I can't figure out is how to find the start number of the phrase, because the phrases are not all the same. Even if you find the "WARRANTY" and the end sign "}" you have no idea where the phrase starts. I tried searching for the "\cf0", but there are multiple of those, so thats a no go.

Nothing associated with VBA comes to mind too.

[sathiyamoorthy]

Hi seolann,

=left(mid(k8,find("with",k8,1),99),find("}",mid(k8,find("with",k8,1),99),1)-1)

try this .....

Let me know where you stuck... I didnt read all others comments...

Sathya

[sathiyamoorthy]

Hi,

try this for latest sheet

=left(mid(a2,find("with",a2,1),99),find("}",mid(a2,find("with",a2,1),99),1)-1)

[bmouse]

The formula itself works, I just had to change the commas to semicolons because of my region, however it does not provide the necessary result. The word "with" is not always used in the target phrase, hence no cigar. That is the main problem here - the only word all of the phrases contain is "warranty" (and also "year") but they differ in lenghth and structure.

Actually, if you change the word "with" to "warranty" it gives YEARS WARRANTY FOR PRINTER, only the starting position is off as \cf0 WITH 6 YEARS WARRANTY FOR PRINTER is the expected result. What else could be done with the formula to adjust the starting position of the phrase?

[samba_ravi]

=MID(A2,LOOKUP(SEARCH("WARRANTY",A2),IF(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),4)="\cf0",ROW(INDIRECT("1:"&LEN(A2)))),IF(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),4)="\cf0",ROW(INDIRECT("1:"&LEN(A2))))),FIND("}\",A2,SEARCH("WARRANTY",A2))-LOOKUP(SEARCH("WARRANTY",A2),IF(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),4)="\cf0",ROW(INDIRECT("1:"&LEN(A2)))),IF(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),4)="\cf0",ROW(INDIRECT("1:"&LEN(A2))))))
Try this array formula
shift+ctrl+enter

[bmouse]

This one is definitely going in my book as one of the longest formulas ever seen

Nice work nflsales

[samba_ravi]

thanks bmouse
B2 =TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,"\cf0",REPT(" ",LEN(A2))&"\cf0"),"}\",REPT(" ",LEN(A2))),SEARCH("WARRANTY",SUBSTITUTE(SUBSTITUTE(A2,"\cf0",REPT(" ",LEN(A2))&"\cf0"),"}\",REPT(" ",LEN(A2))))-LEN(A2),LEN(A2)*2))
try this and copy towards down

[seolann]

Thanks all,

The above formula work. Thank all the great effort!

[samba_ravi]

Thanks for your feedback