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

namename

New Member
Joined
Sep 1, 2020
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. MacOS
  2. Web
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
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 3, 2018
Messages
12,618
Office Version
  1. 2007
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 1, 2020
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. MacOS
  2. Web

ADVERTISEMENT

Here is a screenshoot, I wasn't able to get XL2BB working
Screen Shot 2020-09-01 at 1.56.13 PM.png
 

namename

New Member
Joined
Sep 1, 2020
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. MacOS
  2. Web
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
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Sep 1, 2020
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. MacOS
  2. Web
Do you think it can be affected by the list because there is over 11000 words to screen from
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
That should not make any difference (other than time).
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

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