Forum Discussion

anupambit1797's avatar
anupambit1797
Iron Contributor
Aug 29, 2024
Solved

TEXTSPLIT with multiple Occurances

Dear Experts,                    I have a data as attached and for each cell, I need to extract the Text between ", " and "Mhz".   So for 1st cell : [] for bcs5: [{1, [6470945]}] and featureSetsW...
BI & Data Analysis
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Aug 29, 2024

    anupambit1797 

    That could be

    =ARRAYTOTEXT(TOROW(TEXTAFTER(TEXTBEFORE(TEXTSPLIT(A1,"{"), "Mhz" ),","),3))

    if you are on Excel 365

anupambit1797's avatar
anupambit1797
Iron Contributor
Aug 30, 2024

Thanks djclements , I tried to use both of them, but in my spread sheet it's giving me #SPILL! err

 

Thanks & Regards,

Anupam Shrivastava

djclements's avatar
djclements
Silver Contributor
Aug 30, 2024

anupambit1797 My variant was written to work with your sample file, which did not contain a table. Since your actual data appears to be formatted as a structured Excel table, the BYROW function will not work. Instead, just reference "This Row" of the "Merged" column:

 

=ARRAYTOTEXT(DROP(TEXTAFTER(TEXTSPLIT([@Merged], "Mhz/"), ", ", -1),, -1))

//OR

=TEXTJOIN(",",, DROP(TEXTAFTER(TEXTSPLIT([@Merged], "Mhz/"), ", ", -1),, -1))

 

Most of the formulas shared on this thread will return the same results for the sample data you've provided, so it really comes down to personal preference. Cheers!

Resources