Forum Discussion
anupambit1797
Jun 04, 2025Steel Contributor
Text Extract - 2
Dear Experts,
I have a txt file as attached in Excel, from where need to extract the meaningful data like below(Logic and what to extract is like below)
Each cell Identity has a MeasQuantityresults for rsrp,rsrq and sinr, followed by the rsrp,rsrq and sinr for ssbIndex for the same cellIdentity as below till a new cellIdentity is measured and so on.. :-
By the way , if someone can educate also on what format would these(txt) be? in what language do we use these nested kind of pattern.. any easier method to read them ?
Any PQ or legacy formula Welcomed ... as I don't have REGRX in my excel , but I do have Python supported in my excel version, so regex in python also more than Welcome to achieve the output.
Thanks in Advance!!
Br,
Anupam
Perhaps like this
11 Replies
Sort By
- Patrick2788Silver Contributor
I know you're looking for a PQ or legacy solution but I'd handle this with a Lambda. The Lambda created is portable and can be moved to any workbook where needed.
SignalSplitλ = LAMBDA(table_col, LET( //Assign header header, { "MeasQuantityResults-CellIdentity","rsrp","rsrq","sinr","", "ssbIndex","rsrp","rsrq","sinr"}, //Wrap vector - 1 record = 19 rows wrapped, WRAPROWS(table_col, 19, ""), //Concat and covert back to vector joined, BYROW(wrapped, CONCAT), //Remove double quotes to simplify extraction cleaned, SUBSTITUTE(joined, """", ""), //Function to extract text between two delimiters TextBetweenλ, LAMBDA(texts, after_delim, before_delim, TEXTBEFORE(TEXTAFTER(texts, after_delim, , , , ""), before_delim, , , , "") ), //Extract text between delimiters: cell_identity, TextBetweenλ(cleaned, "310/260-", ","), rsrp, TextBetweenλ(cleaned, "rsrp: ", ","), rsrq, TextBetweenλ(cleaned, "rsrq: ", ","), sinr, TextBetweenλ(cleaned, "sinr: ", " }"), ssb_index, TextBetweenλ(cleaned, "ssbIndex: ", ","), //"Space" vector to be added in middle of return matrix space, EXPAND("", ROWS(rsrp), , ""), //Stack columns return, VSTACK( header, HSTACK(cell_identity, rsrp, rsrq, sinr, space, ssb_index, rsrp, rsrq, sinr) ), return ) );
At the sheet level the formula is:
- anupambit1797Steel Contributor
Thanks Patrick2788 , but seems some data is missing, example the rsrp,rsrq and sinr measurements for the ssbindex 3,2,1,0 for this CellIdentity 833748994 is missing:-
Br,
Anupam
- Patrick2788Silver Contributor
I think I have what you're looking for with the extraction. The return can be polished a bit more if needed but I think the ssbindex is there. Please see revised workbook.
- Patrick2788Silver Contributor
The uploaded workbook doesn't contain the embedded .txt file. Is the text sample from JSON?
- anupambit1797Steel Contributor
Please find attached the zip txt..
- Patrick2788Silver Contributor
The forum may have zapped the attachment.
This is what I see: