Excel formula err lookup

  • Hi,
    Working on something new, no vba this time.


    In excel cell A:A ( full A coulmn) i have various unsystematic strings
    A1 - SQL_78999_uvhj
    A2 - HBU_VVVVV_55555_jjjjj
    A3 - THE_ABC123_LOP
    A4 - ABC5635_12345_KFG


    REQUIREMENT-


    1) If there is 5 Contious digit in a sentence get that ( below formula works for me)
    2)if something is starting with ABC get that word ( eg ABC 123,ABC5635) (below formula works for me
    3) if in a sentence there is both above criteria get both answer in format of / ( eg - answer of A4 should be ABC5635/12345..... ( NEED HELP ON THIS)
    4) if nothing is found put " NHA"


    Formula used -


    =if error (LOOKUP(2,1/(MMULT(0+(ISNUMBER(-MID(" "&A1,ROW(INDIRECT("1:"&LEN(A1)))+{0,1,2,3,4,5,6},1))+0={0,1,1,1,1,1,0}),{1;1;1;1;1;1;1})=7),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),5))
    Mid (find ( A1," ABC"), 1) 8)

  • Re: Excel formula err lookup


    Does this work?


    [COLOR="#0000FF"]=IF(LEFT(A1,3)="ABC",LEFT(A1,FIND("_",A1)-1)&"/","")&IFERROR(LOOKUP(2,1/(MMULT(0+(ISNUMBER(-MID(" "&A1,ROW(INDIRECT("1:"&LEN(A1)))+{0,1,2,3,4,5,6},1))+0={0,1,1,1,1,1,0}),{1;1;1;1;1;1;1})=7),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),5)),"NHA")[/COLOR]


    CSE confirmed and copied down.

    Where there is a will there are many ways. Finding one that works for you is the challenge!