Forum Discussion

manhuy's avatar
manhuy
Copper Contributor
Mar 14, 2023
Solved

Extract text from Comma Separated Cell to another Comma Separated Cell

Hi Community,   I was wondering if anyone could help me with this problem.  Any method to achieve "Desired Outcome" column would be appreciated. Data Desired Outcome BAR-Barcelona (1), RM-...
excel
Formulas and Functions
Macros and VBA
  • JosWoolley's avatar
    JosWoolley
    Mar 14, 2023

    manhuy 

     

    =LET(
    ζ, TEXTSPLIT(A1, "-", " "),
    TEXTJOIN(", ", , FILTER(TAKE(ζ, , 1), 1 - ISNA(TAKE(ζ, , -1))))
)

     

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Mar 14, 2023

manhuy 

As variant

=TEXTJOIN(", ",,  FILTERXML("<t><s>"&  SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(B2,"-","</s><s>"),",","</s><s>"), " ", "")     &"</s></t>", "//s[string-length()<4]") )

for

Resources