extract text from within a text string

[ozhunter]

Hello

I am trying to extract text from the following text string example. I want to return from two places after the comma, to 2 places before the N. The comma is constant, from the N in NSW to the end will always be the same number of spaces but often different text (could be NSW 2795 or NSW 2800 or Vic 3454) the other text will vary. So from this example, I want NEW YORK. The result is not on 'stock register list'! but another sheet

28 CURUMBIN PLACE, NEW YORK NSW 2000

I have been working with

Please Login or Register  to view this content.

but I just can't get it. Any suggestions?

Cheers

[Andrew-R]

Something like this, perhaps:

=MID('stock register list'!J2,SEARCH(",",'stock register list'!J2)+2,FIND("NSW",'stock register list'!J2)-FIND(",",'stock register list'!J2)-3)

[ozhunter]

Andrew, that would work if NSW was constant, but it could be any one of three or four variants. The last 8 digits will always be the same length and layout ie XXX XXXX

[Andrew-R]

Sorry, that's what I get for not reading posts properly

=MID('stock register list'!J2,SEARCH(",",'stock register list'!J2)+2,LEN('stock register list'!J2)-FIND(",",'stock register list'!J2)-10)

[ozhunter]

Thankyou Andrew, works well. Apparantly I was very close a couple of times, but missed it by that much.

Cheers

[Kyle123]

why is everyone better at formulas than me? The best I could do was a rather verbose:

=LEFT(MID('stock register list'!J2,FIND(",",'stock register list'!J2)+2,LEN('stock register list'!J2)),LEN(MID(F14,FIND(",",'stock register list'!J2)+2,LEN('stock register list'!J2)))-8)  

[Andrew-R]

Kyle, possibly your formula is verbose because you've introduced cell F14 into the mix, which hadn't previously been mentioned

[Kyle123]

haha a curse on my rubbish copy and paste skills - possibly even worse than my formulas!

[ozhunter]

At least you have enough gumption to post a proposed fix. By the time I worked out an issue, the poster would be dead and windows 1000 operating system will be here.