Remove contents from a cell based on certain conditions

[mlarson]

Hello! I am hoping somebody can help me with this problem. Thank you in advance for your help!

I am looking for a formula (prefer not to use VBA) that will accomplish the following, with the formula/results in column B.

When the first 5 characters of a cell are repeated within that same cell, I would like for all text/characters that are after that second occurrence of those first 5 characters, including those 5 characters itself, to be cleared/removed. If there is no second occurrence of those first 5 characters, then nothing needs to be cleared/removed. Here are some examples...

Current cell (A1):	(pizza 345) bananas are yellow (pizza 345) grass is green sky is blue
Desired result (B1):	(pizza 345) bananas are yellow
Note:	"(pizz" are the first 5 characters, so everything after second occurrence of "(pizz", including itself, is removed.
Current cell (A2):	(1 pizza 345) bananas are yellow (1 pizza 345) grass is green sky is blue
Desired result (B2):	(1 pizza 345) bananas are yellow
Note:	"(1 pi" are the first 5 characters, so everything after second occurrence of "(1 pi", including itself, is removed.
Current cell (A3):	(pizza 345) bananas (p) are yellow (pizza 345) grass is green sky is blue
Desired result (B3):	(pizza 345) bananas (p) are yellow
Note:	"(pizz" are the first 5 characters, so everything after second occurrence of "(pizz", including itself, is removed.
Current cell (A4):	(pizza 345) bananas (pizz are yellow (pizza 345) grass is green (p) sky is blue
Desired result (B4):	(pizza 345) bananas
Note:	"(pizz" are the first 5 characters, so everything after second occurrence of "(pizz", including itself, is removed.
Current cell (A5):	(pizza 345) bananas are yellow grass is green (p) sky is blue
Desired result (B5):	(pizza 345) bananas are yellow grass is green (p) sky is blue
Note:	"(pizz" not repeated in the cell, so nothing cleared/removed.

Hopefully I've explained that clearly enough. Thank you for your help!
-Mark

 

[Snakehips]

@mlarson Mark, try this.

Book1
	A	B
1	(pizza 345) bananas are yellow (pizza 345) grass is green sky is blue	(pizza 345) bananas are yellow
2	(1 pizza 345) bananas are yellow (1 pizza 345) grass is green sky is blue	(1 pizza 345) bananas are yellow
3	(pizza 345) bananas (p) are yellow (pizza 345) grass is green sky is blue	(pizza 345) bananas (p) are yellow
4	(pizza 345) bananas (pizz are yellow (pizza 345) grass is green (p) sky is blue	(pizza 345) bananas
5	(pizza 345) bananas are yellow grass is green (p) sky is blue	(pizza 345) bananas are yellow grass is green (p) sky is blue
Sheet1

Cell Formulas
Range	Formula
B1:B5	B1	=TRIM(LEFT(SUBSTITUTE(A1,LEFT(A1,5),REPT(" ",999),2),999))

Hope that helps.

 

[mlarson]

That worked perfectly, thank you so much!

 

[Peter_SSs]

This would also be an option I believe.

22 03 06.xlsm
	A	B
1	(pizza 345) bananas are yellow (pizza 345) grass is green sky is blue	(pizza 345) bananas are yellow
2	(1 pizza 345) bananas are yellow (1 pizza 345) grass is green sky is blue	(1 pizza 345) bananas are yellow
3	(pizza 345) bananas (p) are yellow (pizza 345) grass is green sky is blue	(pizza 345) bananas (p) are yellow
4	(pizza 345) bananas (pizz are yellow (pizza 345) grass is green (p) sky is blue	(pizza 345) bananas
5	(pizza 345) bananas are yellow grass is green (p) sky is blue	(pizza 345) bananas are yellow grass is green (p) sky is blue
Clear text

Cell Formulas
Range	Formula
B1:B5	B1	=TRIM(LEFT(A1,FIND(LEFT(A1,5),A1&A1,2)-1))

Just to clarify:
To strictly do what you have asked, my formula should not have the TRIM function around it as, in the first example, this removes the space character after the word "yellow" and that space character is not part of the first 5 characters, nor after that.
I have assumed that you would want that space removed though so that the final text ends with "yellow" not "yellow "

A somewhat similar issue occurs with @Snakehips' formula, though if TRIM were removed from that formula, there would be many space characters at the end of the text.