Allow only characters, numbers and spaces in a cell

ishanwagmi

New Member
Joined
Feb 28, 2014
Messages
9
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.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,754
Hi

Remark: When you post a formula with names, please post their definition or else we are not sure of what's happening.

---

Well, I don't see why the space is not allowed, the space is a character like any other.

2 possible options:
- you forgot to include the space in the String
- what you think is a space is not really a space but some other invisible character like the html non-breaking space

Please confirm.

If you still have problems please post
- the String
- a text that is not working

In both cases don't type them, copy them directly from your worksheet.
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,392

ishanwagmi

New Member
Joined
Feb 28, 2014
Messages
9
Dear etaf,
The above formula allows special char if inserted in after typing allowed characters.
E.G.
1asdfgTRUE
2%%%%FALSE
3asdfg%%TRUE
41234%%%TRUE
5%%%qwetFALSE
612345TRUE

<colgroup><col><col><col></colgroup><tbody>
</tbody>

For case 4 above it shows true, which is not acceptable as per my SAP system.
Can you help me with this?

Regards,
Ishan.
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,392
try

=AND(LEN(A1) < 41,ISNUMBER(SUMPRODUCT(FIND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ "))))
 

ishanwagmi

New Member
Joined
Feb 28, 2014
Messages
9
Thank you Wayne,
I just checked the thread and it's very helpful.
By the way last night I found out another method of accomplishing this task.
With help of a friend I made a VBA programming that creates a function "alphanumeric"

The code is:


Function AlphaNumeric(pValue) As Boolean<o:p></o:p>

Dim LPos As Integer<o:p></o:p>
Dim LChar As String<o:p></o:p>
Dim LValid_Values As String<o:p></o:p>
<o:p></o:p>
'Start at first character in pValue<o:p></o:p>
LPos = 1<o:p></o:p>
<o:p></o:p>
'Set up values that are considered to be alphanumeric<o:p></o:p>
LValid_Values = " abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ+-.0123456789"<o:p></o:p>
<o:p></o:p>
'Test each character in pValue<o:p></o:p>
While LPos <= Len(pValue)<o:p></o:p>
<o:p></o:p>
'Single character in pValue<o:p></o:p>
LChar = Mid(pValue, LPos, 1)<o:p></o:p>
<o:p></o:p>
'If character is not alphanumeric, return FALSE<o:p></o:p>
If InStr(LValid_Values, LChar) = 0 Then<o:p></o:p>
AlphaNumeric = False<o:p></o:p>
Exit Function<o:p></o:p>
End If<o:p></o:p>
<o:p></o:p>
'Increment counter<o:p></o:p>
LPos = LPos + 1<o:p></o:p>
<o:p></o:p>
Wend<o:p></o:p>
<o:p></o:p>
'Value is alphanumeric, return TRUE<o:p></o:p>
AlphaNumeric = True<o:p></o:p>
<o:p></o:p>
End Function



But I will be using the formula only.
Thanks,
Ishan
<o:p></o:p>
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,511
Office Version
2010
Platform
Windows
By the way last night I found out another method of accomplishing this task.
With help of a friend I made a VBA programming that creates a function "alphanumeric"

The code is:

Function AlphaNumeric(pValue) As Boolean<o:p></o:p>

Dim LPos As Integer<o:p></o:p>
Dim LChar As String<o:p></o:p>
Dim LValid_Values As String<o:p></o:p>
<o:p></o:p>
'Start at first character in pValue<o:p></o:p>
LPos = 1<o:p></o:p>
<o:p></o:p>
'Set up values that are considered to be alphanumeric<o:p></o:p>
LValid_Values = " abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ+-.0123456789"<o:p></o:p>
<o:p></o:p>
'Test each character in pValue<o:p></o:p>
While LPos <= Len(pValue)<o:p></o:p>
<o:p></o:p>
'Single character in pValue<o:p></o:p>
LChar = Mid(pValue, LPos, 1)<o:p></o:p>
<o:p></o:p>
'If character is not alphanumeric, return FALSE<o:p></o:p>
If InStr(LValid_Values, LChar) = 0 Then<o:p></o:p>
AlphaNumeric = False<o:p></o:p>
Exit Function<o:p></o:p>
End If<o:p></o:p>
<o:p></o:p>
'Increment counter<o:p></o:p>
LPos = LPos + 1<o:p></o:p>
<o:p></o:p>
Wend<o:p></o:p>
<o:p></o:p>
'Value is alphanumeric, return TRUE<o:p></o:p>
AlphaNumeric = True<o:p></o:p>
<o:p></o:p>
End Function
Here is a one-liner function that will return the same results as the function you posted above (note I named my function IsAlphaNumeric instead of what you named yours)..

Code:
Function IsAlphaNumeric(S As String) As Boolean
  IsAlphaNumeric = Not S Like "*[!a-zA-Z0-9.+-]*"
End Function
 

ishanwagmi

New Member
Joined
Feb 28, 2014
Messages
9
Thanks Rick,
This is an awesome forum. I think someone should develop a mobile aap for this.
It will allow easy access to the forum.

Thanks,
Ishan
 

Momanda

New Member
Joined
Jul 12, 2017
Messages
5
try

=AND(LEN(A1) < 41,ISNUMBER(SUMPRODUCT(FIND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ "))))
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!
 

Forum statistics

Threads
1,082,500
Messages
5,365,935
Members
400,863
Latest member
kimtid

Some videos you may like

This Week's Hot Topics

Top