0

I want to find an easy way to extract text between characters multiple times within the same cell. So in the example below, I want to take the information between the parenthesis and get them into the adjacent column, as shown. Is there a fast way to do this?

My process has been to use Text to Columns to separate them out into different cells, then use a mid function to get what's between the parenthesis, and string them all back together. I'm wondering if someone can think of a better way to do this. Thank you!

I should note-- I want to do this entirely within excel.

enter image description here

Jarhead
  • 91
  • 1
  • 1
  • 4

2 Answers2

1

For example:

enter image description here

Formula in B1:

=TEXTJOIN(CHAR(10),1,LEFT(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1," (",CHAR(10)),CHAR(10),"</s><s>")&"</s></t>","//s[substring(.,1,3)*0=0]"),3))

Enter as array!

If you are interested in FILTERXML, you might want to have a look at this.

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Shame, I suppose your Excel does not yet support TEXTJOIN. You could still access the resulting Filterxml array through Index, bit no longer in the same cell. I'll have a look tomorrow. Can you tell me your version of Excel? – JvdV Jun 10 '20 at 23:20
0

Assuming the 1st data is in A1, put :

=REPLACE(A1,1,FIND("(",A1),"") in C1

then,

=IFERROR(REPLACE(C1,FIND(")",C1),FIND("(",C1)-FIND(")",C1)+1," "),C1) in D1

and drag D1 to Q1. lastly, put 

=Q1 in B1.

That shall do it.

Idea : replace all text between the ) & ( with a space, 15 times.

hope it helps.

p._phidot_
  • 1,913
  • 1
  • 9
  • 17
  • Thank you! I think this is getting closer... using those formulas gave me this output. B2: 123 456) C2: 789) D2: 012 345 456) – Jarhead Jun 10 '20 at 22:20
  • you may click on the tick to remove this from 'unanswered' question list (if it solves..).. If it is not.. share the thoughts.. ( ; – p._phidot_ Jun 10 '20 at 22:24
  • Not there yet... I'm still getting the end ")" in the cell, and the numbers are not appearing on different lines in the cell – Jarhead Jun 10 '20 at 22:31
  • "appearing on different lines" needs a special character. I don't how to inset it in this formula.. | "...getting the end ")" in the cell " > do `=MID(Q1,1,FIND(")",Q1)-1)` in B1 – p._phidot_ Jun 10 '20 at 22:37