Extract Middle Word from a Text String with / and space

[TheOnlyJme]

Hi,

I need to extract the first location (ie London Luton) out of the below string.
Ive been able to write functions for all the other using left/right and mid, but can get this one to work.

281653.P Smith 19-Jun-13 London Luton/Leeds

I even used the Micorsoft help pags Split text among columns by using functions - Excel - Office.com to no avail, to try and understand how to write the formula.

The nearest I got was to return 'London Luton/Leeds'
=MID(L11,SEARCH(CHAR(127),SUBSTITUTE(L11," ",CHAR(127),3))+1,SEARCH("/",L11,1))

or the first 9 charicters (ie London Lu) after the third space (and I dont know how or why
=MID(L9,SEARCH(" ",L9,SEARCH(" ",L9,SEARCH(" ",L9,1)+1)+1)+1,SEARCH(" ",L9,SEARCH(" ",L9,SEARCH(" ",L9,1)+1)+1)-(SEARCH(" ",L9,SEARCH(" ",L9,1)+1)+1))

 

[BenMiller]

Will there always be two words in the location?

 

[pgc01]

Hi
Welcome to the board

The first thing you have to do is to analyse the strings you have to understand how to identify the first location.

You only posted 1 example, and that's not enough to know what the solution should be.

Since you may have thousands of strings with different formats you must post the logic to use if we want to extract the first location.

For ex., if all the strings have the same format, I'd say the logic is:

Extract the characters between the last digit and the next "/"

Please clarify

 

[Michael M]

Maybe a UDF, if they are 2 words followed by a Fwd Slash
But I betting this will be improved on in a couple of minutes
and use

=ext(A1)

Function ext(s As String) As String 'extracts uppercase string out of text
With CreateObject("VBScript.RegExp")
.Pattern = "(\s\b[A-z]{1,}\s[A-z]{1,})"
If .Test(s) Then ext = .Execute(s)(0).SubMatches(0)
End With
End Function

 

[TheOnlyJme]

Hi See a selection of data examples below - thanks

281418.J Hallington 14-Jun-13 Meadowhall/Leeds
281363.L Clarke 24-Jun-13 Wakefield Westgt/London Kings Cros
281347.A Smith-Jones 28-Apr-13 Htl/Prague
281426.J Phillps 01-Aug-13 Liverpool Lime Street/London Euston
281319.A Jones 19-Jun-13 Leeds./Manchester Piccadi
281287.P Hill 21-Jun-13 London Kings Cross/Yichun Shi

 

[Michael M]

Hi PGC
I knew you'd sniff this one out....and now you're going to give me another Regex lesson....ray:
and I just noticed the OP's set of examples....so that does me in anyway.....I'd be too slow at working out the rest...

 

[ukmikeb]

Hi PGC
I just noticed the OP's set of examples....so that does me in anyway.....I'd be too slow at working out the rest...

Where's your sense of adventure Michael?

Hi
Try :-

=MID(A21,FIND("@",SUBSTITUTE(A21,"-","@",LEN(A21)-LEN(SUBSTITUTE(A21,"-",""))))+4,FIND("/",A21)-(FIND("@",SUBSTITUTE(A21,"-","@",LEN(A21)-LEN(SUBSTITUTE(A21,"-",""))))+4))

change the cell reference as you wish.

hth

 

[TheOnlyJme]

Fantastic!! Quick response and bang on

 

[ukmikeb]

You're welcome.

Thanks for the feedback.

Just in case of the possibility of a location being hyphenated, try :-

=MID(A21,FIND("-13 ",A21)+4,FIND("/",A21)-(FIND("-13 ",A21)+4))

Pleased to have helped solve your problem.

Good luck with your project.

 

[Michael M]

Nice one Mike....One day, when I get smarter....I'm gonna learn ALL about Excel .....ray: