# screen only the first 4 letters of a word and compare to a list of bad first letters

#### namename

##### New Member
I need help understanding how to screen names
I have tried many other formulas however it was not specific enough.

I am looking for formula that will screen only the first 4 letters of a word and compare to a list of bad first letters (A57 List). When it screens the name it will flag as "FAIL" as it has not passed the screening process.

I have tried IF statements, searching and many different formulas. The all end up screening the whole word and I just need the first 4 letters to not match.

Thank you!

*Update*
Used:
Excel Formula:
`` =IF(ISNA(MATCH(LEFT(C3,4),Y2:Y5,0)),"Ok","FAIL")``

When comparing to the full A57 List it was flagging words that should not have failed as "FAIL".
This may be issues with case sensitivity. I need it not to be case sensitive because multiple people will be inputting information into the file.

When using MATCH working on desktop excel however did not on Online Excel

### Excel Facts

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

#### Fluff

##### MrExcel MVP, Moderator
That formula is not case sensitive.
Can you post a sample of your data using the XL2BB add-in that shows where it's failing.

#### DanteAmor

##### Well-known Member
Hi and welcome to MrExcel

Try this:

Libro1
CDY
2scrae
3screenOKrainbow
4world
5SCRExd
Hoja4
Cell Formulas
RangeFormula
D3D3=IFERROR(IF(MATCH(LEFT(C3,4)&"*",Y2:Y5,0)>0,"OK"),"FAIL")

#### Fluff

##### MrExcel MVP, Moderator
Something that could cause the formula to give the wrong result, is if the values in col Y are not exactly 4 characters in length

#### namename

##### New Member

Here is a screenshoot, I wasn't able to get XL2BB working

#### namename

##### New Member
Something that could cause the formula to give the wrong result, is if the values in col Y are not exactly 4 characters in length

You think I should weed out all the letters less than 4, on my list I extracted the first 4 letters however some words does not have 4 letters

#### Fluff

##### MrExcel MVP, Moderator

The formula works for me, for the 4character "words"

Book(1).xlsx
ABCNOPXY
1Naming ProcessArtical 57 List
2NameNamingNNA57A 31
3welcomeOKA L
4ehndOKA R
5abdoFAILA.T.
6historyFAILA.Vo
7AcovFAILAaa
8ACOVFAILAaci
9acoverFAILAafa
10abbacityFAILAaja
11ABBAcityFAILAara
12a 31567FAILAass
13a.t.123FAILAbac
14Abak
15Abal
16Aban
Sheet1
Cell Formulas
RangeFormula
O3:O13O3=IF(ISNA(MATCH(LEFT(C3,4),Y:Y,0)),"OK","FAIL")

#### namename

##### New Member
Do you think it can be affected by the list because there is over 11000 words to screen from

#### Fluff

##### MrExcel MVP, Moderator
That should not make any difference (other than time).

#### Fluff

##### MrExcel MVP, Moderator
Another option would be to setup a range name using this formula
=OFFSET(Sheet1!\$Y\$2,,,COUNTA(Sheet1!\$Y:\$Y)-1)
and then you could use this formula in the sheet
=IF(SUM(COUNTIF(C3,FailList&"*")),"FAIL","Ok")

where FailList is the range name

Book(1).xlsx
ABCMNOPXY
1Naming ProcessArtical 57 List
2NameNamingN#NNA57A 31
3welcomeOkOKA L
4ehndOkOKA R
5abdoFAILFAILA.T.
6historyFAILFAILA.Vo
7AcovFAILFAILAaa
8ACOVFAILFAILAaci
9acoverFAILFAILAafa
10abbacityFAILFAILAaja
11ABBAcityFAILFAILAara
12a 31567FAILFAILAass
13a.t.123FAILFAILAbac
14abedfgFAILOKAbak
15Abal
16Aban
Sheet1
Cell Formulas
RangeFormula
N3:N14N3=IF(SUM(COUNTIF(C3,FailList&"*")),"FAIL","Ok")
O3:O5,O7:O13O3=IF(AND(ISNA(MATCH(LEFT(C3,4),Y:Y,0)),ISNA(MATCH(LEFT(C3,3),Y:Y,0))),"OK","FAIL")
O6,O14O6=IF((ISNA(MATCH(LEFT(C6,4),Y:Y,0))),"OK","FAIL")
Named Ranges
NameRefers ToCells
FailList=OFFSET(Sheet1!\$Y\$2,,,COUNTA(Sheet1!\$Y:\$Y)-1)N3:O14

Replies
5
Views
229
Replies
2
Views
243
Legacy 456155
L
Replies
2
Views
118
Replies
0
Views
56
Replies
1
Views
51

1,126,998
Messages
5,622,093
Members
415,875
Latest member
Tarali

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