Hello,
I'd like to extract a string inside excel cells. I do no know how to extract exact number of characters (14). Please see example C.
Thanks for your support
regards
Mark
Example A: CORRECT FORMULA
Original text: IRU - BRE280100-CAPEX - ACQ. IRU (FW150000) - 614517-I02KH00-F02C0100510102
Result: F02C0100510102
Formula: =IF(ISNUMBER(SEARCH("F02K";BP2));TRIM(LEFT(SUBSTITUTE(MID(BP2;FIND("F02K";BP2&"-F02K");1000);"*";REPT("";1000));1000));IF(ISNUMBER(SEARCH("F02C";BP2));TRIM(LEFT(SUBSTITUTE(MID(BP2;FIND("F02C";BP2&"-F02C");1000);"*";REPT("";1000));1000));TRIM(LEFT(SUBSTITUTE(MID(BP2;FIND("F02N";BP2&"-F02N");1000);"*";REPT("";1000));1000))))
Example B: CORRECT FORMULA
Original text: IT02-cables - SRE110200-OPEX - leasing - 635120-I02NH20
Result: (empty)
Formula: =IF(ISNUMBER(SEARCH("F02K";BP2));TRIM(LEFT(SUBSTITUTE(MID(BP2;FIND("F02K";BP2&"-F02K");1000);"*";REPT("";1000));1000));IF(ISNUMBER(SEARCH("F02C";BP2));TRIM(LEFT(SUBSTITUTE(MID(BP2;FIND("F02C";BP2&"-F02C");1000);"*";REPT("";1000));1000));TRIM(LEFT(SUBSTITUTE(MID(BP2;FIND("F02N";BP2&"-F02N");1000);"*";REPT("";1000));1000))))
Example C: WRONG FORMULA
Original text: Maintenance IT - SLI330101-Selling- Various- 626325-I02DF000-F02K0042572900-7798532032-D57D19000910001
Result: F02K0042572900-7798532032-D57D19000910001
Formula: =IF(ISNUMBER(SEARCH("F02K";BP2));TRIM(LEFT(SUBSTITUTE(MID(BP2;FIND("F02K";BP2&"-F02K");1000);"*";REPT("";1000));1000));IF(ISNUMBER(SEARCH("F02C";BP2));TRIM(LEFT(SUBSTITUTE(MID(BP2;FIND("F02C";BP2&"-F02C");1000);"*";REPT("";1000));1000));TRIM(LEFT(SUBSTITUTE(MID(BP2;FIND("F02N";BP2&"-F02N");1000);"*";REPT("";1000));1000))))
Hi Mark,
Here is one solution.
It may not be 100% effective as we don't have all the necessary information.
BR,
Lionel
Perfect ! thank you very much for your support
I changed the formula in this way:
=IFERROR(MID(A3;FIND("F02K";A3);14);IFERROR(MID(A3;FIND("F02N";A3);14);IFERROR(MID(A3;FIND("F02N";A3);14);" ")))
I forgot to mention some strings contain other "F02", i.e.
Original text: Maintenance IT - SLI330101-Selling- Various- 626325-F02DF000-F02K0042572900-7798532032-D57D19000910001
Regards
Mark
