Formula to find last occurrence of a word and return the word + 10 characters

[willow1985]

I need help with a formula to find the last occurrence of a word, return that word plus 10 characters.

Keep in mind the source text will change but one thing that will remain consistent is the words I want to return will always be on the end and will state data in this order with this many characters: OPS 123 Rev 1

See below example:

Book1
	A	B	C
1	Source	Current Formula	Desired Result
2	X:\QUALITY\Group\OPS Forms\Space\[OPS 277 Rev 3 - Workbook Series.xlsm]Data	OPS Forms\Spac	OPS 277 Rev 3
Sheet1

Cell Formulas
Range	Formula
B2	B2	="O"&MID(A2, FIND("OPS", A2) + 1,13)

Help on this formula would be greatly appreciated.

Thank you to anyone who can help!

 

[Eric W]

Try:

=LET(x,TEXTSPLIT(A2,"OPS"),c,COLUMNS(x),"OPS"&LEFT(INDEX(x,c),10))

 

[Fluff]

How about

=LEFT(TEXTAFTER(A2,"[",-1),13)

 

[willow1985]

How about

=LEFT(TEXTAFTER(A2,"[",-1),13)

This is perfect, short and simple. Thank you so much, had a mental block on how to do this.

 

[Fluff]

Glad we could help & thanks for the feedback.