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

djclements's avatar
djclements
Silver Contributor
Aug 29, 2024

anupambit1797 One more variant to add to the mix:

 

=BYROW(A1:A2101, LAMBDA(r, ARRAYTOTEXT(DROP(TEXTAFTER(TEXTSPLIT(r, "Mhz/"), ", ", -1),, -1))))

 

If you don't like the extra space added to the delimiter by ARRAYTOTEXT, use TEXTJOIN instead:

 

=BYROW(A1:A2101, LAMBDA(r, TEXTJOIN(",",, DROP(TEXTAFTER(TEXTSPLIT(r, "Mhz/"), ", ", -1),, -1))))

 

Cheers!

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