Help with IF, LEN, LEFT, FIND Formula

[zarealshook]

Hello Everyone,

Could anyone please assist me to correct the below formula?

=IFERROR(IF(OR(LEN(LEFT(A2,FIND("-",A2)-0))=5,LEN(LEFT(A2,FIND("-",A2)-0))=4),"FOUND","NOT FOUND"),"NOT FOUND")

I have worked on the formula, but it doesn't seem to work fine for all data. It worked for some and failed for some.

Data in column (A) has product numbers in different length and arrangement.

I would like to assign column (B) to display "Found" whenever it finds the following two product formats:

1- XXXX-11111-000 (4 Letters at beginning, followed by "-" and 5 digits, followed by "-" and 3 digits at the end) - Should display "FOUND"

2- XXX-11111-000 (3 Letters at beginning, followed by "-" and 5 digits, followed by "-" and 3 digits at the end) - Should display "FOUND"

All other products should display "NOT FOUND"

700-800-900-000
375-64-36
397-0-531
302-0230
300-5788
3008-453
FDCX-36514-850
30-06-937
300-6-9-38
DPZH-55121-001
DPZ-12412-315
LABEL B/YELLOW 18 M
77001-00030
60352010005425
JD70-99VA/ZKM-E09QL4-F

Any early response is appreciated,,,

Regards,

Zare

 

[etaf]

your not testing for the 3 letters at the end

302-0230

300-5788

would meet the formula, but does not have 3 digits the end

FDCX-36514-850
But has two - is that OK ?

=LEFT(RIGHT(A2,4),1)="-"
would find if - was the 4th from end
with and AND to your OR

=IFERROR(IF(AND( LEFT(RIGHT(A2,4),1)="-" , OR(LEN(LEFT(A2,FIND("-",A2)-0))=5,LEN(LEFT(A2,FIND("-",A2)-0))=4)),"FOUND","NOT FOUND"),"NOT FOUND")

Book2
	A	B
1
2	700-800-900-000	FOUND
3	375-64-36	NOT FOUND
4	397-0-531	FOUND
5	302-0230	NOT FOUND
6	300-5788	NOT FOUND
7	3008-453	FOUND
8	FDCX-36514-850	FOUND
9	30-06-937	NOT FOUND
10	300-6-9-38	NOT FOUND
11	DPZH-55121-001	FOUND
12	DPZ-12412-315	FOUND
13	LABEL B/YELLOW 18 M	NOT FOUND
14	77001-00030	NOT FOUND
15	6.0352E+13	NOT FOUND
16	JD70-99VA/ZKM-E09QL4-F	NOT FOUND
Sheet1

Cell Formulas
Range	Formula
B2:B16	B2	=IFERROR(IF(AND( LEFT(RIGHT(A2,4),1)="-", OR(LEN(LEFT(A2,FIND("-",A2)-0))=5,LEN(LEFT(A2,FIND("-",A2)-0))=4)),"FOUND","NOT FOUND"),"NOT FOUND")

 

[Snakehips]

I had assumed the '3 digits at the end' to be three digits following the second - ????

If so then maybe

Book1
	A	B
1
2	700-800-900-000	NOT FOUND
3	375-64-36	NOT FOUND
4	397-0-531	NOT FOUND
5	302-0230	NOT FOUND
6	300-5788	NOT FOUND
7	3008-453	NOT FOUND
8	FDCX-36514-850	FOUND
9	30-06-937	NOT FOUND
10	300-6-9-38	NOT FOUND
11	DPZH-55121-001	FOUND
12	DPZ-12412-315	FOUND
13	LABEL B/YELLOW 18 M	NOT FOUND
14	77001-00030	NOT FOUND
15	6.0352E+13	NOT FOUND
16	JD70-99VA/ZKM-E09QL4-F	NOT FOUND
17	1234-12345-1234	NOT FOUND
18	abcd-1234-123	NOT FOUND
19
HRIS

Cell Formulas
Range	Formula
B2:B18	B2	=IF(A2="","",IF(OR(AND(MID(A2,5,1)="-",MID(A2,11,1)="-",LEN(A2)=14),AND(MID(A2,4,1)="-",MID(A2,10,1)="-",LEN(A2)=13)),"FOUND","NOT FOUND"))

Of course, this doesn't check that the first group of characters are letters and the others are number. If this is important then the formula will get somewhat bigger / messier and a vba UDF will likely be the way to go.

 

[zarealshook]

your not testing for the 3 letters at the end

302-0230

300-5788

would meet the formula, but does not have 3 digits the end

FDCX-36514-850
But has two - is that OK ?

=LEFT(RIGHT(A2,4),1)="-"
would find if - was the 4th from end
with and AND to your OR

=IFERROR(IF(AND( LEFT(RIGHT(A2,4),1)="-" , OR(LEN(LEFT(A2,FIND("-",A2)-0))=5,LEN(LEFT(A2,FIND("-",A2)-0))=4)),"FOUND","NOT FOUND"),"NOT FOUND")

Book2
	A	B
1
2	700-800-900-000	FOUND
3	375-64-36	NOT FOUND
4	397-0-531	FOUND
5	302-0230	NOT FOUND
6	300-5788	NOT FOUND
7	3008-453	FOUND
8	FDCX-36514-850	FOUND
9	30-06-937	NOT FOUND
10	300-6-9-38	NOT FOUND
11	DPZH-55121-001	FOUND
12	DPZ-12412-315	FOUND
13	LABEL B/YELLOW 18 M	NOT FOUND
14	77001-00030	NOT FOUND
15	6.0352E+13	NOT FOUND
16	JD70-99VA/ZKM-E09QL4-F	NOT FOUND
Sheet1

Cell Formulas
Range	Formula
B2:B16	B2	=IFERROR(IF(AND( LEFT(RIGHT(A2,4),1)="-", OR(LEN(LEFT(A2,FIND("-",A2)-0))=5,LEN(LEFT(A2,FIND("-",A2)-0))=4)),"FOUND","NOT FOUND"),"NOT FOUND")

your not testing for the 3 letters at the end

302-0230

300-5788

would meet the formula, but does not have 3 digits the end

FDCX-36514-850
But has two - is that OK ?

=LEFT(RIGHT(A2,4),1)="-"
would find if - was the 4th from end
with and AND to your OR

=IFERROR(IF(AND( LEFT(RIGHT(A2,4),1)="-" , OR(LEN(LEFT(A2,FIND("-",A2)-0))=5,LEN(LEFT(A2,FIND("-",A2)-0))=4)),"FOUND","NOT FOUND"),"NOT FOUND")

Book2
	A	B
1
2	700-800-900-000	FOUND
3	375-64-36	NOT FOUND
4	397-0-531	FOUND
5	302-0230	NOT FOUND
6	300-5788	NOT FOUND
7	3008-453	FOUND
8	FDCX-36514-850	FOUND
9	30-06-937	NOT FOUND
10	300-6-9-38	NOT FOUND
11	DPZH-55121-001	FOUND
12	DPZ-12412-315	FOUND
13	LABEL B/YELLOW 18 M	NOT FOUND
14	77001-00030	NOT FOUND
15	6.0352E+13	NOT FOUND
16	JD70-99VA/ZKM-E09QL4-F	NOT FOUND
Sheet1

Cell Formulas
Range	Formula
B2:B16	B2	=IFERROR(IF(AND( LEFT(RIGHT(A2,4),1)="-", OR(LEN(LEFT(A2,FIND("-",A2)-0))=5,LEN(LEFT(A2,FIND("-",A2)-0))=4)),"FOUND","NOT FOUND"),"NOT FOUND")

Thank you very much etaf for your help. However, it is necessary that the formula should check the last 3 digits (after the "-") too.

When I applied the formula above it also doesn't give the correct outcome I need.

Appreciate your support.

 

[zarealshook]

first group of characters are letters and the others are number

Thanks a million Snakehips! You saved my life!! The formula works a treat.

Keep up the good work

Zare