jamescooper
Well-known Member
- Joined
- Sep 8, 2014
- Messages
- 840
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.
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.