Formula to match State within a string of text and return the result

Hi everybody,

I have been trying to find a solution to this problem with no luck. I have wasted so many hours and don't know else to do but ask for help.

In the file i have attached, I am trying to match the state (could be any one of NSW, VIC, WA, QLD, SA, TAS, ACT, NT) from the text within the url in column G and return the result in column C.

Hopefully my explanation makes sense.

Any help or assistance would be appreciated.

Thanks,
Futurefight

Would the state code always be between the 6th and 7th + symbol in the url or is that just the case in the attached sample data?

BSB

List the state codes in a column somewhere. For the sake of this example I've put them in cells M1:M8.
Then use the below formula in C1 and copy down as required.

Formula:

Please Login or Register  to view this content.

See attached.

BSB

Thank you for this. At first it looked to be working great but now I have noticed there is something wrong happening. Some data is present but it is returning #REF!
and it also seems to be returning the wrong results but I cant work out exactly why, I have attached an updated example.

For example, in the attached sample c6 is returning TAS when it should be QLD.

Thanks,
Futurefight

Ali is correct. Your CSV attachment shows my original formula but the data is in a different layout (i.e. there's a header row and the url is in a different column to your original file).

If you correct everything to point in the right place it will work in most instances, however when you have urls that contain two of the possible codes then the formula falls over. i.e. in your second attachment they should all be QLD however if any of them contain the word 'Service' then they also contain 'VIC'. This is why the need for a more representative sample in the first place.

Her second formula will solve that as it's looking for only instances of the state code with a + symbol directly before and after. However, there is no need to confirm it with Ctlr+Shift+Enter to make it an array formula as stated. The SUMPRODUCT element of it will handle that.

On a separate note, if the details in your second attachment are not desensitized and contain real details of real people/companies, you may want to remove that attachment from the thread. K22 for example clearly shows a mobile phone number and putting data like that up on the internet for public consumption is probably not wise. It would more than likely break some data protection law, certainly here in the UK....

BSB

Aha! Well that makes sense. But I'm not using that version and from the bio details I'm not sure the OP is either

BSB

Couldn't agree more!


You may wish to have a look at my last point in post #7.


BSB.

Thank you all again for your help, the solution was found in this formula suggested by AliGW:
=INDEX(M$1:M$8,SUMPRODUCT(ISNUMBER(SEARCH("+"&M$1:M$8&"+",K2))*ROW(M$1:M$8)))