0

Following the query below:

Excel extract one word from the any part of the string

I have attempted to extract more than one substring from the middle of my text in Excel.

The string looks like this:

1 Willow Court, 1192 Christchurch Road, Bournemouth, Dorset, BH7 6EG

I have effectively 2 solutions. Both are at most partially working. One solution uses the FILTERXML function, mentioned here:

Excel - Extract substring(s) from string using FILTERXML

where I used the following formula:

     =FILTERXML("<t><s>"&SUBSTITUTE(A1,", ","</s><s>")&"</s></t>","//s[position()=2 or position()=3]")

I got the result but in the separate formulas, as this results returns multiple values. It has been spilled. I want everything in one cell.

enter image description here

Another approach comes from:

https://www.ablebits.com/office-addins-blog/2016/06/01/split-text-string-excel/

and what I have done here is:

    =MID(A1, SEARCH(",",A1) + 1, SEARCH(",",A1,SEARCH(",",A1)+1) - SEARCH(",",A1)-1)

As a return I have received:

1192 Christchurch Road

After the small modification of my formula:

   =MID(A4, SEARCH(",",A4) + 1, SEARCH(",",A4,SEARCH(",",A4,SEARCH(",",A4)+1)+1) - SEARCH(",",A4)-1)

by adding one SEARCH element I could receive:

1192 Christchurch Road, Bournemouth

It's fine but there is one major problem with this formula. The middle string comes always after the first comma. What should I change here to receive the substring from the second comma and so on?

I tried to do something like this:

   =MID(A1, SEARCH(",",A1,2) + 1, SEARCH(",",A1,2,SEARCH(",",A1,SEARCH(",",A1,2)+1)+1) - SEARCH(",",A1,2)-1)

but it didn't work, as well as other trials:

   =MID((A1, SEARCH(",",A1) + 1, SEARCH(",",A1,SEARCH(",",A1,SEARCH(",",A1)+1)+1) - SEARCH(",",A1)-1),2)

   =MID(A1, SEARCH(",",A1) + 2, SEARCH(",",A1,SEARCH(",",A1,SEARCH(",",A1)+2)+2) - SEARCH(",",A1)-1)

If you can look at my full address trying above, could you tell me is there any possibility to apply the MID function to the substring, which falls after the second comma and so on?

How can I get rid of the spilled formula above?

JvdV
  • 70,606
  • 8
  • 39
  • 70
Geographos
  • 827
  • 2
  • 23
  • 57
  • 1
    If you got spilling results you can join the array together using `TEXTJOIN()`. --> `=TEXTJOIN(", ",,FILTERXML(""&SUBSTITUTE(A1,", ","")&"","//s[position()=2 or position() =3]"))`. – JvdV Jan 26 '21 at 10:04
  • Ok good, but how about the second approach? Is there any way to get the data from the second comma? – Geographos Jan 26 '21 at 10:53

0 Answers0