# Help with IF, LEN, LEFT, FIND Formula

#### zarealshook

##### New Member
Hello Everyone,

Could anyone please assist me to correct the below formula?

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"

 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

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### etaf

##### Well-known Member
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

Book2
AB
1
2700-800-900-000FOUND
4397-0-531FOUND
73008-453FOUND
8FDCX-36514-850FOUND
11DPZH-55121-001FOUND
12DPZ-12412-315FOUND
Sheet1
Cell Formulas
RangeFormula

#### Snakehips

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

If so then maybe

Book1
AB
1
8FDCX-36514-850FOUND
11DPZH-55121-001FOUND
12DPZ-12412-315FOUND
19
HRIS
Cell Formulas
RangeFormula

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

Book2
AB
1
2700-800-900-000FOUND
4397-0-531FOUND
73008-453FOUND
8FDCX-36514-850FOUND
11DPZH-55121-001FOUND
12DPZ-12412-315FOUND
Sheet1
Cell Formulas
RangeFormula
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

Book2
AB
1
2700-800-900-000FOUND
4397-0-531FOUND
73008-453FOUND
8FDCX-36514-850FOUND
11DPZH-55121-001FOUND
12DPZ-12412-315FOUND
Sheet1
Cell Formulas
RangeFormula
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.

#### zarealshook

##### New Member
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

1,127,328
Messages
5,624,036
Members
416,007
Latest member
csf

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

### Which adblocker are you using?

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

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