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

• 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!