0

Using an MS Excel formula (No VBA/Macro), I would like to extract between the Nth and Nth characters and/or letters in a string. Example: I have text in Columns A2 and A3, I would like to extract text located between the 4th space and 9th space in each of the following strings.

Column A2: Johnny went to the store and bought an apple and some grapes

Column A3: We were not expecting to go home so early but we had no other choice due to rain

Results:

Column A2: store and bought an apple

Column A3: to go home so early

Miaka3
  • 339
  • 1
  • 10
  • 27

2 Answers2

1

With Microsoft 365 or Excel 2019 you can use TEXTJOIN() in combination with FILTERXML():

enter image description here

Formula in B1:

=TEXTJOIN(" ",,FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s[position()>4][position()<6]"))

The xpath expressions first selects all elements from the 5th word onwards and consequently only returns the first 5 elements from that respective array. Therefor //s[position()>4][position()<6] can also be written as //s[position()>4 and position()<10].

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Thanks JvdV, this is absolutely works... I would like to know, based on your fabulous solution using FitlerXML, am I able to interchange or substitute the search parameters to extract text at any given position. Example, I would like to swap "Spaces" and extract between the 3 comma in the text and 10th space. – Miaka3 Jun 24 '21 at 12:58
  • Depending on how your data looks I think I'd look at a slight variant of the above in any such case nesting a couple of `SUBSTITUTE()` functions. @Miaka3. However, you may want to include sample data and preverably your own attempt at solving this. – JvdV Jun 24 '21 at 13:03
  • Thanks for your reply. Per my question and your reply, I have text in each cell and may need to extract between two different data points. In this sample text provided, I would like to know with your awesome provided, how can I change the search criteria from "spaces" to another. Example Extract between 2nd Comma and 10 space: "We had a great evening, but when it started to rain we had to leave, however, we did have fun. Later that evening I went home and didn't leave the house until the next morning." RESULTS: ", however we did have fun. Later that evening I went" – Miaka3 Jun 24 '21 at 13:13
  • For any such query I think I'd look into using the 4th parameter in `SUBSTITUTE()` to indicate which occurence you want to change per delimiter. One for the comma, one for a space, for example: `=FILTERXML(""&SUBSTITUTE(SUBSTITUTE(A1,",","",2)," ","",10)&"","//s[2]")` @Miaka3 – JvdV Jun 24 '21 at 13:16
  • I'm literally and seriously smiling at the screen, wanting to shake your hand. What I've been stubbornly attempting for hours not wanting assistance, was solved in only minutes. .. unbelievable thanks JvdV – Miaka3 Jun 24 '21 at 13:21
  • @Miaka3, no worries. No handshakes untill the vacination though! Btw, `=TEXTJOIN(" ",,FILTERXML(""&SUBSTITUTE(MID(A1,FIND("|",SUBSTITUTE(A1,",","|",2)),LEN(A1))," ","")&"","//s[position()<12]"))` was needed to get your exact wanted result as per your 2nd to last comment. If `FILTERXML()` helped you out, you may like [this](https://stackoverflow.com/q/61837696/9758194) post. – JvdV Jun 24 '21 at 13:22
  • Agreed... Thank you for your cooperation in solving my Excel questions.. And, yes the provided formula works like a charm. – Miaka3 Jun 24 '21 at 13:33
0

What you are looking for is a substring function, which is not really available in excel, I would suggest looking at this tutorial for functions that perform the same functionality https://www.excel-easy.com/examples/substring.html, and try to find the best one for your use case. I would think the MID function would be the best suit for your use case.

Katout999
  • 61
  • 4