Find from the Right

[jamescooper]

I would like to find the position of Swansea and return the time after it, in the following string:

Severn Tunnel East 0/39; Severn Tunnel West 0/42½; Severn Tunnel Junction 0/43½ [Plat:3]; Runs ML to Llanwern West Junction 0/49; -> [2];Ebbw Jn 1/00; -> [2];Runs RL to Port Talbot Parkway 1c55½; -> [2];Neath 2c05½; -> [3];Swansea Loop East 2/18; Swansea 2.20 [Plat:2].

Is anyone able to help? When I do it with this formula it returns the first instance with Swansea Loop East. Is anyone able to help and determine from the right instead?

=IF(RIGHT(E36,11)="and as bkd.","",IF(G36="","",IF(E36="","",IFERROR(MID(E36,SUM((FIND(D36,E36,2))+LEN(D36)+1),2)&":"&MID(E36,SUM((FIND(D36,E36,2))+LEN(D36)+4),2),MID(E37,SUM((FIND(D36,E37,2))+LEN(D36)+1),2)&":"&MID(E37,SUM((FIND(D36,E37,2))+LEN(D36)+4),2)))))

Thanks.

 

[steve the fish]

Try something like this.

=MID(A1,FIND("~",SUBSTITUTE(A1,"Swansea ","Swansea~",2))+1,5)

 

[jamescooper]

Wow thanks Steve, that is really helpful. Much appreciated, what a great forum!

 

[jamescooper]

Can I just ask one more thing.

Say instead of SwanseaI want it to look in a particular cell, say A2 where Swansea could be. Could you let me know what I would replace Swansea with.

A2, A2~

?

 

[Momentman]

Like this maybe =MID(A1,FIND("~",SUBSTITUTE(A1,A2,A2&"~",2))+1,5)

Let me also add my very LENGTHY formula, the difference is that the first formula works only if there are 2 mentions of Swansea in the phrase which am not sure will always be the case, so i incorporated a way to find how many Swansea's are there and replace the last occurrence of it

Excel 2010
	A	B
1	Severn Tunnel East 0/39; Severn Tunnel West 0/42; Severn Tunnel Junction 0/43 [Plat:3]; Runs ML to Llanwern West Junction 0/49; -> [2];Ebbw Jn 1/00; -> [2];Runs RL to Port Talbot Parkway 1c55; -> [2];Neath 2c05; -> [3];Swansea Loop East 2/18; Swansea 2.20 [Plat:2].	2.20
2	Swansea
Sheet3

Cell Formulas
Range	Formula
B1	=TRIM(MID(A1,FIND(" ",A1,FIND("|?",SUBSTITUTE(A1,A2,"|?",(LEN(A1)-LEN(SUBSTITUTE(A1,A2,"")))/LEN(A2)))+1),5))

 

[jamescooper]

Very impressive! Thanks a lot, I was thinking of just doing a count and if it is greater than 1, take a certain formula, otherwise the other.