1

I have a cell in Excel with text as follows

text1 > text2 > text3 > text4 > text5 > text6

I need to find a rule to extract the some of the text and put it in another cell for example like this (the order may vary):

text6 text3 text5

I tried different solutions but they don't really work as expected:

=TRIM(MID(D6,FIND("#",SUBSTITUTE(D6,">","#",2))+1,255))

=REPLACE(D7,1,FIND("^^",SUBSTITUTE(D7,">","^^",8))+1,"")

=RIGHT(D4,LEN(D4)-SEARCH("#",SUBSTITUTE(D4,">","#",LEN(D4)-LEN(SUBSTITUTE(D4,">","")))))&" "&MID(D4, FIND(CHAR(1),SUBSTITUTE(D4,">",CHAR(1),3))+2, FIND(CHAR(1),SUBSTITUTE(D4,">",CHAR(1),3)) - FIND(CHAR(1),SUBSTITUTE(D4,">",CHAR(1),2))-11)

Can you help me with that? Thanks

EDIT: Found the solution! This rule is working fine for me. If you change the value of X you change the number of the occurrencies:

=SUBSTITUTE(MID(SUBSTITUTE(">" & D5&REPT(" ",6),">",REPT(",",255)),X*255,255),",","")
Vincenzo Coppola
  • 11
  • 2
  • 2
    Use [FILTERXML](https://stackoverflow.com/questions/61837696/excel-extract-substrings-from-string-using-filterxml) – Scott Craner Jan 26 '21 at 17:50
  • 1
    *I need to find a rule to extract the some of the text* No, you need to develop a rule, and then code it. Without knowing the rule or rules you want to use, one would need to be a good guesser to figure what rules might be appropriate. I don't really see how you expect to obtain the results you show from your data input. – Ron Rosenfeld Jan 26 '21 at 19:56
  • @RonRosenfeld Do you have any recommendations for where to start? What functions are most appropriate to solve this problem? Thank you – Vincenzo Coppola Jan 27 '21 at 15:44
  • Found the solution, thanks for the help. – Vincenzo Coppola Jan 27 '21 at 17:57

0 Answers0