Excel extract one word from the any part of the string

Question

I have the string containing the address, which sections are divided by commas.

Outside Locksbrook Campus (Bath Spa University) Locksbrook Road, Bath, CB1 3QQ located in the D19 cell

I would like to extract the name of the city from this string, which is "Bath". The name of the city is always to be placed before the postcode.

I have managed with it in 2 steps. First I extracted everything before the last comma, like here:

Excel IF statement for trim function

  =MID(D19,1,FIND("@",SUBSTITUTE(D19,",","@",LEN(D19)-LEN(SUBSTITUTE(D19,",","")))))

which gave me:

Outside Locksbrook Campus (Bath Spa University) Locksbrook Road, Bath,

and next, I had to extract the last word in my new string by using this formula:

  =TRIM(RIGHT(SUBSTITUTE(AA5," ",REPT(" ",100)),100))

which gave me pretty much the final result:

Bath,

However, I would like to make it done with 1 operation only.

Is it possible at all?

I found some formula here, which should have to extract any word (the nth word) from the string, but regrettably, it doesn't work in my case.

https://exceljet.net/formula/extract-nth-word-from-text-string

   =TRIM(MID(SUBSTITUTE(D19," ",REPT(" ",LEN(D19))), (N-1)*LEN(D19)+1, LEN(D19)))

it gives me #NAME? finally.

Moreover, I have misgivings, that it won't work, when the address string will be slightly different, containing more elements like block name, street name, city, postcode or only street name, city, and postcode.

Is there any way to extract only one section from the string before the last comma (after the pre ultimate comma)?

question from:https://stackoverflow.com/questions/65626333/excel-extract-one-word-from-the-any-part-of-the-string

Answer 1

You may try:

=FILTERXML("<t><s>"&SUBSTITUTE(D19,", ","</s><s>")&"</s></t>","//s[position()=last()-1]")

The xpath expression //s[position()=last()-1] tells the function to return the s-node that is second to last by position index.

For more information on FILTERXML() and the use of some xpath to return substrings of interest, you may find this interesting.