Extract substring in excel|General Excel Questions & Answers|Excel Forum|My Online Training Hub

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))))