TekBytz

Excel’s built-in functions can do some pretty clever stuff, but unfortunately Excel has no single function that will return the  string beween nth and n+1th charecter occurrence of specified data. Fortunately, there are ways to make Excel do this. The function used could be complex but could be accomblished.

Consider if you have the text “How to find the substring in Excel” in cell A1 and incase if you need to find 5th word in the string the below formula can be used. This can be used for strings with any seperators

=IF(ISERR(MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),4))+1,(FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),5))-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),4))-1))),"",MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),4))+1,(FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),5))-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),4))-1)))

 As a Generic formula the below paramters can be changed to achive to find any word between occurances

=IF(ISERR(MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"C",CHAR(1),n))+1,(FIND(CHAR(1),SUBSTITUTE(A1,"C",CHAR(1),m))-FIND(CHAR(1),SUBSTITUTE(A1,"C",CHAR(1),n))-1))),"",MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"C",CHAR(1),n))+1,(FIND(CHAR(1),SUBSTITUTE(A1,"C",CHAR(1),m))-FIND(CHAR(1),SUBSTITUTE(A1,"C",CHAR(1),n))-1)))

 Where

A1 – Cell of the string

C – is the Charecter occurance, can be ” “, “.” etc

n – Nth occurance of Charecter C, n starts from 0

m – N+1 occurance of Charecter C, m starts from 0

 Few other useful formulas that would handle strings.

Finding the Nth position of a charecter

=FIND(CHAR(1),SUBSTITUTE(A1,”i”,CHAR(1),3))

This formula will inf the 3rd occurance if charecter “i” in cell A1.

Alternte formula with similar function

=SEARCHB("i",A1,(SEARCHB("i",A1,(SEARCHB("i",A1,1)+1))+1))

Finding the first Word in a String

=IF(ISERR(MID(A1,1,FIND(" ",A1)-1)),"",MID(A1,1,FIND(" ",A1)-1))

This formula will find the 1st word in a sting in cell A1.