Results 1 to 5 of 5

Data Validation - No Spaces

This is a discussion on Data Validation - No Spaces within the Excel Questions forums, part of the Question Forums category; I would like to not allow a user to enter spaces in a series of characters in a cell. I ...

  1. #1
    New Member
    Join Date
    May 2006
    Posts
    24

    Default Data Validation - No Spaces

    I would like to not allow a user to enter spaces in a series of characters in a cell. I currently have the data validation set to text length = 7. There should be no spaces within those 7 characters. Is there a way to use the Data Validation function for this and still keep the text length limitation? I could probably write some code to have a message pop up but thought there might be another way. I don't want to just strip out the spaces or replace them with zeros because the sequence really needs to be seven characters and match another sequence of characters elsewhere. The user needs to be forced to enter seven characters without spaces. I will need to have this in 2003 and 2007. I think the solution will be the same for both versions.

    Thanks for any help.

  2. #2
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,793

    Default Re: Data Validation - No Spaces

    Try using Data Validation with this custom formula (assuming data in A1)

    =(LEN(A1)=7)*(LEN(SUBSTITUTE(A1," ",""))=7)

  3. #3
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,265

    Default Re: Data Validation - No Spaces

    A similar one:

    =(LEN(A1)=7)*(SUBSTITUTE(A1," ","")=A1)
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  4. #4
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,793

    Default Re: Data Validation - No Spaces

    Quote Originally Posted by pgc01 View Post
    A similar one:

    =(LEN(A1)=7)*(SUBSTITUTE(A1," ","")=A1)
    Ah, yes, that's better......

  5. #5
    New Member
    Join Date
    May 2006
    Posts
    24

    Default Re: Data Validation - No Spaces

    Thanks, PGC and Barry.

    This works:
    =(LEN(A1)=7)*(SUBSTITUTE(A1," ","")=A1)

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com