Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Data Validation-Accepting First & Last Names

    I Would like to use Data Validation, Custom list to set up a formula where only a first Name and last name will be accepted for eg David Smith, Frank Marks etc

    Your assistance will be most appreciated

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I assume you mean a custom formula, a list is different than a formula. A formula like:
    =ISNUMBER(FIND(" ",A1))

    Will require that a space be in the entry, which seems to be the criteria you are interested in.

    Steve

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks for the reply. I would like to amend rthe formula under custom formula to accept First Names and last names where the first and last names start with upper case e.g Dave Smith

    Regards

    Howard

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If you use the formula:
    =AND(ISNUMBER(FIND(" ",A1)),CODE(LEFT(A1,1))>64, CODE(LEFT(A1,1))<91, CODE(MID(A1,FIND(" ",A1)+1,1))>64,CODE(MID(A1,FIND(" ",A1)+1,1))<91)

    The conditions (in the order above)
    must have space,
    the first letter must be at least a capital A
    the first letter must be less than a Capital Z
    the first letter after the space must be at least a capital A
    the first letter after the space must be less than a Capital Z

    Steve

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks for the help, formula works perfectly

    Regards

    Howard

Posting Permissions

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