Help with IF, LEN, LEFT, FIND Formula

zarealshook

New Member
Joined
May 26, 2014
Messages
3
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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,575
Office Version
  1. 365
Platform
  1. MacOS
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
AB
1
2700-800-900-000FOUND
3375-64-36NOT FOUND
4397-0-531FOUND
5302-0230NOT FOUND
6300-5788NOT FOUND
73008-453FOUND
8FDCX-36514-850FOUND
930-06-937NOT FOUND
10300-6-9-38NOT FOUND
11DPZH-55121-001FOUND
12DPZ-12412-315FOUND
13LABEL B/YELLOW 18 MNOT FOUND
1477001-00030NOT FOUND
156.0352E+13NOT FOUND
16JD70-99VA/ZKM-E09QL4-FNOT FOUND
Sheet1
Cell Formulas
RangeFormula
B2:B16B2=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

Well-known Member
Joined
May 17, 2009
Messages
5,103
Office Version
  1. 2013
Platform
  1. Windows
I had assumed the '3 digits at the end' to be three digits following the second - ????

If so then maybe

Book1
AB
1
2700-800-900-000NOT FOUND
3375-64-36NOT FOUND
4397-0-531NOT FOUND
5302-0230NOT FOUND
6300-5788NOT FOUND
73008-453NOT FOUND
8FDCX-36514-850FOUND
930-06-937NOT FOUND
10300-6-9-38NOT FOUND
11DPZH-55121-001FOUND
12DPZ-12412-315FOUND
13LABEL B/YELLOW 18 MNOT FOUND
1477001-00030NOT FOUND
156.0352E+13NOT FOUND
16JD70-99VA/ZKM-E09QL4-FNOT FOUND
171234-12345-1234NOT FOUND
18abcd-1234-123NOT FOUND
19
HRIS
Cell Formulas
RangeFormula
B2:B18B2=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

New Member
Joined
May 26, 2014
Messages
3
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
AB
1
2700-800-900-000FOUND
3375-64-36NOT FOUND
4397-0-531FOUND
5302-0230NOT FOUND
6300-5788NOT FOUND
73008-453FOUND
8FDCX-36514-850FOUND
930-06-937NOT FOUND
10300-6-9-38NOT FOUND
11DPZH-55121-001FOUND
12DPZ-12412-315FOUND
13LABEL B/YELLOW 18 MNOT FOUND
1477001-00030NOT FOUND
156.0352E+13NOT FOUND
16JD70-99VA/ZKM-E09QL4-FNOT FOUND
Sheet1
Cell Formulas
RangeFormula
B2:B16B2=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
AB
1
2700-800-900-000FOUND
3375-64-36NOT FOUND
4397-0-531FOUND
5302-0230NOT FOUND
6300-5788NOT FOUND
73008-453FOUND
8FDCX-36514-850FOUND
930-06-937NOT FOUND
10300-6-9-38NOT FOUND
11DPZH-55121-001FOUND
12DPZ-12412-315FOUND
13LABEL B/YELLOW 18 MNOT FOUND
1477001-00030NOT FOUND
156.0352E+13NOT FOUND
16JD70-99VA/ZKM-E09QL4-FNOT FOUND
Sheet1
Cell Formulas
RangeFormula
B2:B16B2=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,683
Members
415,920
Latest member
ExcelNoob28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top