Text Extract - 2

Question

Dear Experts,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 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..&nbsp; :-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 ?&nbsp;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

patrick2788 · Answer

I know you're looking for a PQ or legacy solution but I'd handle this with a Lambda.&nbsp; 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
        )
    );&nbsp;At the sheet level the formula is:&nbsp;

anupambit1797 · Answer

Attaching it again .. let me know if you can see the attachment&nbsp;

patrick2788 · Answer

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.

patrick2788 · Answer

The uploaded workbook doesn't contain the embedded .txt file.&nbsp; Is the text sample from JSON?

anupambit1797 · Answer

Please find attached the zip txt..

Forum Discussion

Text Extract - 2

9 Replies

Resources