A Areif Member Oct 14, 2021 #1 Hello all !! Hope all are safe and doing well , i got stuck at finding the repeated words in sentence. hope here i get the solution... reference sheet is attached Thanks in advanced Attachments Chandoo.xlsx 8.4 KB · Views: 10
Hello all !! Hope all are safe and doing well , i got stuck at finding the repeated words in sentence. hope here i get the solution... reference sheet is attached Thanks in advanced
B bosco_yip Excel Ninja Oct 15, 2021 #2 In D3, enter array (CSE) formula : =TEXTJOIN(", ",1,FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(LOWER(B3),".","")," ","</b><b>")&"</b></a>","//b[preceding::* =.][not(following::* =.)]"))
In D3, enter array (CSE) formula : =TEXTJOIN(", ",1,FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(LOWER(B3),".","")," ","</b><b>")&"</b></a>","//b[preceding::* =.][not(following::* =.)]"))
B bosco_yip Excel Ninja Oct 15, 2021 #4 Areif said: Thanks for reply , but getting #NAME? error, please suggest solution. Click to expand... I think your excel version do not have TEXTJOIN function. Then, try this helper columns ways, 1] In "Helper" E3, formula copied right until blank : =IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(LOWER($B3),".","")," ","</b><b>")&"</b></a>","//b[preceding::* =.][not(following::* =.)]["&COLUMN(A1)&"]"),"") 2] In "Result" D3, enter formula : =TRIM(E3&" "&F3&" "&G3&" "&H3&" "&I3&" "&J3&" "&K3&" "&L3&" "&M3&" "&N3&" "&O3&" "&P3) Attachments ExtractRepeatedWord.xlsx 11.9 KB · Views: 8
Areif said: Thanks for reply , but getting #NAME? error, please suggest solution. Click to expand... I think your excel version do not have TEXTJOIN function. Then, try this helper columns ways, 1] In "Helper" E3, formula copied right until blank : =IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(LOWER($B3),".","")," ","</b><b>")&"</b></a>","//b[preceding::* =.][not(following::* =.)]["&COLUMN(A1)&"]"),"") 2] In "Result" D3, enter formula : =TRIM(E3&" "&F3&" "&G3&" "&H3&" "&I3&" "&J3&" "&K3&" "&L3&" "&M3&" "&N3&" "&O3&" "&P3)
A Areif Member Oct 15, 2021 #5 Thanks for your kind support , its working fine now Kudos to team Chandoo..