slicing between n and n+1th occurrence of String

[alabatusa]

Hi All,

Quick question:

I have the following data in excel cell:
"alpha.beta.charlie.delta.epsilon.foxtrot.gamma"

I wish to retrieve the value between the last and second last dot("."). That in this case is Foxtrot

I have done several methods to do this, such as using a nested find function in this case to find the the position of the 5th dot and and 6th dot. Once i have the positions of each simply use a mid function between these 2 locations to find "Foxtrot"

The problem with this however is that the number of dots in a text will change, and i always wish to pick up the value between the last and second last dot.

e.g in another case if i have "av.ac.ad.o"
i would want to return "ad"

Is there any easy way to do this, without using vba or plugins

Thanks
Atif

 

[sanrv1f]

Try this

Excel Workbook
	A	B
1	alpha.beta.charlie.delta.epsilon.foxtrot.gamma	foxtrot
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B1	=TRIM(LEFT(SUBSTITUTE(MID(A1,SEARCH("~~",SUBSTITUTE(A1,".","~",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))-1))+1,255),".",REPT(" ",255)),255))

There might be a much simple solution than this, but my mind is not working

 

[alabatusa]

Thanks, i will try that


If A1 = “alpha.beta.charlie.delta.epsilon.foxtrot.gamma”
Let A2 = LEN(A1)-LEN(SUBSTITUTE(A1,".",""))
= Number of “.” in data
To then return data between 2nd last and last “.”, can be generated in A3.
Where A3 =IFERROR(MID(A1,FIND(CHAR(5),SUBSTITUTE(A1,".",CHAR(5),A2-1))+1,FIND(CHAR(5),SUBSTITUTE(A1,".",CHAR(5),A2))-FIND(CHAR(5),SUBSTITUTE(A1,".",CHAR(5),A2-1))-1),A1)