Extract text from cell with delimiters

[GreenWheels]

I am trying to extract a specific text from a cell with text up to the first delimiter from its left i.e.

(Green Tuesday)-(Yellow Monday)-(Black Friday)(Sunny Sunday)-(Purple Wednesday)

I would like to search for '(Sunny Sunday)' and the text up to the first delimiter ('-') from its left so resulting in

(Black Friday)(Sunny Sunday)

I have searched here but could not find anything that fit my query.

Thanks

[canapone]

Hi

in Excel 2003 you could try

=MID(A1,LARGE(IF(MID(A1,ROW(A$1:A$200),1)="-",ROW(A$1:A$200)),2)+1,LARGE(IF(MID(A1,ROW(A$1:A$200),1)="-",ROW(A$1:A$200)),1)-LARGE(IF(MID(A1,ROW(A$1:A$200),1)="-",ROW(A$1:A$200)),2)-1)

to be confirmed with control+shift+enter

[GreenWheels]

Hi

in Excel 2003 you could try

=MID(A1,LARGE(IF(MID(A1,ROW(A$1:A$200),1)="-",ROW(A$1:A$200)),2)+1,LARGE(IF(MID(A1,ROW(A$1:A$200),1)="-",ROW(A$1:A$200)),1)-LARGE(IF(MID(A1,ROW(A$1:A$200),1)="-",ROW(A$1:A$200)),2)-1)

to be confirmed with control+shift+enter

Hi thanks, this one does not seem to work for me, as I need to find specific text first then get text up to that delimiter.

[AlKey]

Try this
Assuming text string is in A1
Enter in B1

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("(Sunny Sunday)",A1)+LEN("(Sunny Sunday)")-1),"-",REPT(" ",255)),255))

	A	B
1	(Green Tuesday)-(Yellow Monday)-(Black Friday)(Sunny Sunday)-(Purple Wednesday)	(Black Friday)(Sunny Sunday)

Sheet: Sheet1

[GreenWheels]

Try this
Assuming text string is in A1
Enter in B1

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("(Sunny Sunday)",A1)+LEN("(Sunny Sunday)")-1),"-",REPT(" ",255)),255))

	A	B
1	(Green Tuesday)-(Yellow Monday)-(Black Friday)(Sunny Sunday)-(Purple Wednesday)	(Black Friday)(Sunny Sunday)

Sheet: Sheet1

Hi, many thanks for this, this appears to do what I am looking for

[GreenWheels]

This does not work for cells which contains around 6000 characters.

I have tried to tweak the REPT numbers i.e. 4000,4000 but this may work for some cells (sometimes go past the first delimiter to another), and some will give an #VALUE error.

Is there anything else I should try?

Thanks again.

[AlKey]

This does not work for cells which contains around 6000 characters.

I have tried to tweak the REPT numbers i.e. 4000,4000 but this may work for some cells (sometimes go past the first delimiter to another), and some will give an #VALUE error.

Is there anything else I should try?

Thanks again.

You should've mentioned about this earlier.
Try this

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("(Sunny Sunday)",A1)+LEN("(Sunny Sunday)")-1),"-",REPT(" ",LEN(A1))),LEN(A1)))

[GreenWheels]

You should've mentioned about this earlier.
Try this

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("(Sunny Sunday)",A1)+LEN("(Sunny Sunday)")-1),"-",REPT(" ",LEN(A1))),LEN(A1)))

Tried this, and still some values outstanding with #VALUE!

This relates to text which '(Sunny Sunday)" has a hit somewhere in middle of text (6000 chars) and its first delimiter is around 1200 chars away - hope this makes sense to you?

Thanks

[AlKey]

There is a problem with the data quality/inconsistency that would make formula to fail. Unfortunately, I don't have any other solutions.