find nth occurrence and return values

[moira]

Hello! I would like to find the Nth occurrence of a string and return the 3 characters that follow. For example, find the 7th occurrence of the string "ABC" and return the 3 characters that follow - - such as dog. Thank you in advance!

 

[lrobbo314]

Here is one way.

Book1
	H
21	dog
22	ABC red ABC blue ABC purple ABC green ABC yellow ABC pink ABC dog ABC cat ABC lizard
Sheet1

Cell Formulas
Range	Formula
H21	H21	=MID(H22,FIND("~",SUBSTITUTE(H22,"ABC","~",7))+4,3)

 

[duggie33]

Hi moira,

Something like this might work...

Book1.xlsx
	K
22	7
23	ABCDEFABCOLIABCGHIABCDEFABCDEFABCDEFABCLOLABCKLPABCPOSABCDEF
24	LOL
Sheet19

Cell Formulas
Range	Formula
K24	K24	=MID(K23,SEARCH("|",SUBSTITUTE(K23,"ABC","|",K22),1)+3,3)

Hope that helps,

Doug

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have 365 another option

=LEFT(TEXTAFTER(A2,"ABC",7),3)

 

[kvsrinivasamurthy]

Try this

=LEFT(TRIM(MID(SUBSTITUTE("ABC"&A2,"ABC",REPT(" ",500)),500*8,500)),4)