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


    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!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!