Find from the Right

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.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Try something like this.

=MID(A1,FIND("~",SUBSTITUTE(A1,"Swansea ","Swansea~",2))+1,5)
 
Upvote 0
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~

?
 
Upvote 0
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
AB
1Severn 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
2Swansea
Sheet3
Cell Formulas
RangeFormula
B1=TRIM(MID(A1,FIND(" ",A1,FIND("|?",SUBSTITUTE(A1,A2,"|?",(LEN(A1)-LEN(SUBSTITUTE(A1,A2,"")))/LEN(A2)))+1),5))
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,152
Messages
6,170,389
Members
452,323
Latest member
GoJones

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top