Results 1 to 8 of 8

Can you prevent certain characters being entered into cells?

This is a discussion on Can you prevent certain characters being entered into cells? within the Excel Questions forums, part of the Question Forums category; Hi - my question is as in the subject line. I have a sheet which I don't want people to ...

  1. #1
    Board Regular
    Join Date
    Apr 2006
    Posts
    207

    Default Can you prevent certain characters being entered into cells?

    Hi - my question is as in the subject line. I have a sheet which I don't want people to be able to type anything other than letters and numbers. Is there anyway I can prevent the user from typing commas etc?


    thanks for you help

    Rich

  2. #2
    Board Regular
    Join Date
    Jul 2010
    Location
    New Delhi
    Posts
    348

    Default Re: Can you prevent certain characters being entered into cells?

    Try looking up the help regarding Data -> Validation. This is what you can use to prevent users from entering anything but validated data.
    Hope This Helps !
    Prabby
    --------------------
    WannaB MrExcel MVP
    - tRYING tO fIGURE oUT hOW !

  3. #3
    Board Regular schielrn's Avatar
    Join Date
    Apr 2007
    Location
    Cincinnati, Ohio
    Posts
    6,924

    Default Re: Can you prevent certain characters being entered into cells?

    So are they going to be entering more than one character in the cell, such as words and numbers larger than 9? Or are they just putting in A, B, C ... and 1, 2,... 9.. etc?
    Always make a back up copy before trying new code, you never know what you might lose!


    - Posting guidelines, forum rules and terms of use

    -Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    "The world suffers a lot. Not because of the violence of bad people, but because of the silence of good people!"

  4. #4
    Board Regular
    Join Date
    Apr 2006
    Posts
    207

    Default Re: Can you prevent certain characters being entered into cells?

    Hi Prabby

    I have had a play about with data validataion already, and I can work out how to allow values between A and B, but I can't figure out how to disallow certain characters using this feature.

    schielrn, the data which will be entered into the cells varies: specifically, my problem is that some of the fields are for addresses, but I don't want the user to enter "my street, my town" - because it causes problems when importing it into the database (I assume that somewhere along the line it it converted into a .csv, and this then messes up all of the fields.) There are similar problems with apostrophe's, slashes and question marks.

    Hope this clears up any confusion,

    Thanks

    Rich

  5. #5
    Board Regular schielrn's Avatar
    Join Date
    Apr 2007
    Location
    Cincinnati, Ohio
    Posts
    6,924

    Default Re: Can you prevent certain characters being entered into cells?

    Try this as data validation and it is on cell A1:

    =SUM(--ISNUMBER(MID(UPPER(A1),ROW($A$1:$A$255),1)+0))+SUM((CODE(MID(UPPER(A1),ROW(INDIRECT("A1:A"&LEN(A1))),1))>64)*(CODE(MID(UPPER(A1),ROW(INDIRECT("A1:A"&LEN(A1))),1))<91))+SUM(--(CODE(MID(UPPER(A1),ROW(INDIRECT("A1:A"&LEN(A1))),1))=32))=LEN(A1)

    If it comes up with something that this formula currently evaluates to an error just click yes.

    Let me know if that works? It did for me with some sample data.

    Hope that helps.
    Always make a back up copy before trying new code, you never know what you might lose!


    - Posting guidelines, forum rules and terms of use

    -Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    "The world suffers a lot. Not because of the violence of bad people, but because of the silence of good people!"

  6. #6
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,113

    Default Re: Can you prevent certain characters being entered into cells?

    If you want to just account for comma, apostrophe, slash and question mark then another alternative might be:

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,",",""),"'",""),"/",""),"?","")=A1
    Does my a$$ look big in this picture ?

  7. #7
    Board Regular
    Join Date
    Apr 2006
    Posts
    207

    Default Re: Can you prevent certain characters being entered into cells?

    Sorry for not replying guys - I have been away.

    I have just picked up your responses, and I'll thry them out now. Thanks for your help

    Rich

  8. #8
    New Member
    Join Date
    Jul 2013
    Posts
    1

    Default Re: Can you prevent certain characters being entered into cells?

    Hi - I want to be able to apply this restriction to an entire column like all cells in column A, how do I modify the formula for this, because I notice the present formula applies to one cell only and if I have to do data validation for a column it would take me forever. What I have is a column where people enter numbers and alphabets only, I want to restrict them from entering characters like - , * space etc. but this restriction should be for the entire column A. Would appreciate your help. Thanks Mohideen

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