# Find from the Right

#### jamescooper

##### Well-known Member
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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### steve the fish

##### Well-known Member
Try something like this.

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

#### jamescooper

##### Well-known Member
Wow thanks Steve, that is really helpful. Much appreciated, what a great forum!

#### jamescooper

##### Well-known Member
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

##### Well-known Member
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:

#### jamescooper

##### Well-known Member
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.

Replies
1
Views
397

Threads
1,191,584
Messages
5,987,479
Members
440,097
Latest member
Wint

### 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

### 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