Allow only characters, numbers and spaces in a cell

[ishanwagmi]

Hi,

I am stuck with a data validation problem.
I need to validate a cell to allow only characters, numbers and spaces with maximum length of 40 char
e.g. My room no is 402.

I have this formula

=AND(SEARCH(MID(I3,ROW(INDIRECT("1:"&LEN(I3))),1),String),LEN(I3)<=40)

but it doesn't allow space.

Can anyone tell me where I am going wrong.

 

[Gajendran Yadhav]

This worked PERFECTLY for my needs ... but I still need two enhancements to it and thought I'd see if y'all knew of an easy way to adjust this formula so that I can still use it in Data Validation.

I suspect, however, that I'll need to move into VBA with what I'm trying to do.

Columns A and B are LAST NAME and FIRST NAME fields, respectively. I was needing to find a way to disallow special characters (hyphens in names) and this worked.

The big ask:

My maximum available number of rows is 99 for this process. If, among those 99 rows any combination of LAST NAME and FIRST NAME are identical to any previously entered LAST NAME and FIRST NAME combination, then an identifier is needed.

IE: A1/A2 = SMITH JOHN
A25/A26 = SMITH JOHN -> This should return an error for a duplicate entry.
A25/A26 = SMITH JR JOHN -> Would not return an error.


The small ask:

Is there any way to adjust the code to allow a space to be entered, but not at the end of the data being entered?

IE (x = space): A1 = SMITHxJONES is accepted
A1 = SMITHxJONESx is not allowed

Thanks for your help!!! Y'all are the Best!

Dear members,

any assistance in the above query will be much appreciable.