Lookup err

[libin baby]

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)

 

[shrivallabha]

1. Will there be multiple instances of ABC e.g. ABC123_500_ABC456?
2. Are you always going to look at 5 digit numbers? e.g. in ABC123_123456_CDE does 12345 or 23456 count or it is always 5 digits?

If answer to any of these questions is yes then it might be better to use VBA based UDF.

If No, then it will be possible to wrap conditions inside IF.

 

[libin baby]

1. Will there be multiple instances of ABC e.g. ABC123_500_ABC456?
No there will not be multiple instance of ABC in a string.. Only 1 inscl.
2. Are you always going to look at 5 digit numbers? e.g. in ABC123_123456_CDE does 12345 or 23456 count or it is always 5 digits?
To b more clear here... Yes i vl b looking for 5 DIGIT NUMBER continues numbers in a string....( ones tat start wit ABC will not b included in 5 digit count)

Below fromula works for when My 5 continues digit and Ones tat start with ABC both are in different setence.

My requirement is for eg.... ABC45678923_GYHNKLPO_XXXXX_12345_ZZZZZZ
the output shld be "ABC45678923#12345"

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

 

[Deepak]

libin baby

Will you pls share some more sample string & expected output.

 

[libin baby]

Hi deepak,
Sure thing..... below is the example.. A is the input and B is the output
A1 - DGFHLM_ZFGDHTY6_ABC123456789_YHYH
B1 (OUTPUT) - ABC123456789

A2 - FGHKLM_HUIOKPL_UUUUUUUU_ABC987654321
B2 - ABC987654321

A3- SQL_THE_I_ABC852654
B3-ABC852654

A4 - SGH_KLO_78965_YYYY
B4-78965

A5 - SQ_HOP_JJJJ_12345_THE
B5-12345

A6 - OPS_55555_$$$$$
B6 - 55555

A7 - SSSSS_ABC4569999_YYYYY_78912
B7 - ABC4569999#78912

A8 - FFFFFFF_Sssssss_ABC1234567_63521
B8 - ABC1234567#63521

A9 - ABC123_XXXXX_12345
B9- ABC123#12345

A10- Xxxxx_ccccc_hhhhh_uuuuu
B10- NH

 

[XOR LX]

Assuming the number of digits paired with "ABC" does not exceed 10:

=SUBSTITUTE(TRIM("NH "&SUBSTITUTE(" "&IFERROR("ABC"&-LOOKUP(0,-(LEFT(MID(A1,3+FIND("ABC",A1),99),{1,2,3,4,5,6,7,8,9,10})&"**0")),"")&IFERROR("#"&MID(A1,LOOKUP(2,1/(MMULT(0+(ISNUMBER(-MID("ζ"&A1,ROW(INDEX(A:A,1):INDEX(A:A,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),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))),5),"")," #","")),"NH ","")

Increase the upper bound accordingly in the constant:

{1,2,3,4,5,6,7,8,9,10}

if my assumption was incorrect.

Regards

 

[libin baby]

HI XOR LX

very much appreciated your help in this... The formula works but when there is a string where O is attched to ABC it doesn't work
For Eg - A1 - SQL_THE_ABC00123456789_YUP
The out put should be ABC00123456789 , but implementing your formula it's giving me ABC123456789 which is incorrect, i need the Zero... Itz a high critical data nothing can be missed.. I hope you understand the criticality..... Itz excluding the ZEROS.. I banged my head but nothing is coming up in table..

 

[XOR LX]

In every example you have given so far, the only delimiter present is the underscore (_). I presume then that this is always the case?

Regards

 

[NARAYANK991]

Hi ,

See the attached file for a solution using helper columns. The logic is straightforward that you can examine it and have the confidence that it will work ; if it does not , you can understand why it does not , and correct it yourself.

If you cannot use helper cells , ignore the post.

Narayan

 

[bosco_yip]

Herein the modified XOR LX's formula including ZERO in the Output.

In C2 copy down :

=SUBSTITUTE(TRIM("NH "&SUBSTITUTE(" "&LEFT(MID(A2,FIND("ABC",A2&"ABC"),99),FIND("_",MID(A2,FIND("ABC",A2&"ABC"),99)&"_")-1)&IFERROR("#"&MID(A2,LOOKUP(2,1/(MMULT(0+(ISNUMBER(-MID("ζ"&A2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2)))+{0,1,2,3,4,5,6},1))+0={0,1,1,1,1,1,0}),{1;1;1;1;1;1;1})=7),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2)))),5),"")," #","")),"NH ","")

Regards
Bosco