Can you prevent certain characters being entered into cells?

richanor

Active Member
Joined
Apr 8, 2006
Messages
291
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Try looking up the help regarding Data -> Validation. This is what you can use to prevent users from entering anything but validated data.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
If you want to just account for comma, apostrophe, slash and question mark then another alternative might be:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,",",""),"'",""),"/",""),"?","")=A1
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

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
Back
Top