Forum Discussion
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 featureSetsWithUlCellsTwoMimoLayers:CA_n25A-n41C-n66A-n77A and featureSetIndexes:[{6470947, 10Mhz/10Mhz}]] is matched with [3]:[{6470945, 100Mhz/100Mhz}, {6470946, 80Mhz/80Mhz}], n66[A][A]:[n41[C][A]
the output will be =>
10,100,80
In case of 100Mhz/100Mhz, always use the 1st part ,
I tried to use PQ, using Column from Examples, but seems it's not smart enough to understand the delimiters.
Thanks in Advance,
Br,
Anupam
That could be
=ARRAYTOTEXT(TOROW(TEXTAFTER(TEXTBEFORE(TEXTSPLIT(A1,"{"), "Mhz" ),","),3))if you are on Excel 365
12 Replies
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!
Thanks djclements , I tried to use both of them, but in my spread sheet it's giving me #SPILL! err
Thanks & Regards,
Anupam Shrivastava
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!
If you have Microsoft 365,
=LET(split, TRIM(TEXTSPLIT(A1, ",")), ARRAYTOTEXT(TOROW( IFS(ISNUMBER(FIND("Mhz", split)), LEFT(split, FIND("Mhz", split) - 1)), 2)))Table it and then use:
=LET( split, TEXTBEFORE(TEXTSPLIT([@Strings], "Mhz/"), "Mhz", , , , ""), mhz, TOROW(split, 3), TEXTJOIN(", ", , mhz) )Hi Hecatonchire can you please share the same formula for Python in excel?
- Hi,
=WEBSERVICE("https://e.anyoupin.cn/eh3/?preg_match_all_join~(?<=\s)\d+(?=Mhz)~" & A2)
Try this webservice.
