+ Reply to Thread
Results 1 to 10 of 10

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

  1. #1
    Registered User
    Join Date
    09-01-2020
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    4

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

    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.

    Attached is the file.
    Thank you!

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

    Updated Attached File
    Attached Files Attached Files
    Last edited by rosebud_; 09-01-2020 at 11:42 AM. Reason: Updated did not actually work

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,928

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

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. As you are new here, I have done it for you this time.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,928

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

    Maybe this will do what you want?

    =IF(ISNA(VLOOKUP(LEFT(C3,4),$Y$2:$Y$5,1,0)),"","FAIL")

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,080

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

    How about
    =IF(ISNA(MATCH(LEFT(C3,4),Y2:Y5,0)),"Ok","FAIL")

  5. #5
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,212

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

    Or on similar lines
    =IF(COUNTIF($Y$2:$Y$5,LEFT(C3,4)),"fail","")

  6. #6
    Registered User
    Join Date
    09-01-2020
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    4

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

    Thank you so much it is working!

  7. #7
    Registered User
    Join Date
    09-01-2020
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    4

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

    Thank you so much it is working!

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,928

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

    Which is working? You had three solutions offered ...

  9. #9
    Registered User
    Join Date
    09-01-2020
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    4

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

    I tired all 3 and they all work!

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,928

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

    Jolly good!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Screening of data
    By Natixis in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-20-2018, 01:49 PM
  2. Filtering/Screening Data using Userform
    By essex1234 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-10-2014, 06:55 PM
  3. [SOLVED] Linking My Quoted Rates to Job Screening File
    By cychua in forum Excel General
    Replies: 0
    Last Post: 09-03-2012, 02:05 AM
  4. Screening for Duplicates
    By bostnboy06 in forum Excel General
    Replies: 1
    Last Post: 02-05-2007, 06:30 PM
  5. Screening one list for data
    By DD in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-14-2006, 04:55 PM
  6. [SOLVED] Screening Data
    By Raul in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-30-2005, 10:35 AM
  7. [SOLVED] screening regression analysis in Excel
    By Grinch in forum Excel General
    Replies: 1
    Last Post: 07-19-2005, 08:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1