• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find Text and display

Hi, thanks for looking. See attached an extract from a bank statement, from the description, I am wanting a formula to pick out all the SL and SIP information from the cell and put it into a new cell. So for example for cell A1 - there is an "SL" in the cell - so I want the formulae to return "SL 289909", so return the nine digits including the combined letters "SL". Does that makes sense? The ability to change the number of digits easily would be good just in case we need to increase the numbers.
 

Attachments

  • SL_SIP.xlsx
    10 KB · Views: 7
The attached is a somewhat long complicated solution based upon Excel 365.
Code:
= MAP(description, ParseItemλ)

ParseItemλ = LAMBDA(aDescription,
    LET(
        line,   TEXTSPLIT(aDescription, {" ", "-", "+", ","}),
        next,   RotateLeftλ(line),
        values, XLOOKUP({"SL", "SIP"}, line, next, ""),
        TEXTJOIN(", ", , IF(values <> "", {"SL ", "SIP "} & values, ""))
    )
);

RotateLeftλ = LAMBDA(array,
    LET(n, COLUMNS(array), k, 1 + MOD(SEQUENCE(1, n), n), INDEX(array, k))
);
 

Attachments

  • SL_SIP.xlsx
    16.5 KB · Views: 5
Back
Top