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.

 

[pgc01]

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]

is there a minimum number, here we have between 3 and 40
AND(AND(LEN(A5) >2,LEN(A5) <41),OR(AND(CODE(A5) >=65,CODE(A5) <=90),AND(CODE(A5) >=97,CODE(A5) <=122),AND(CODE(A5) >=48,CODE(A5) <=57),CODE(A5)=32))

ASCII code 49, 1 , number one, American Standard Code for Information Interchange, The complete ASCII table, characters,letters, vowels with accents, consonants, signs, symbols, numbers number, one,ascii,49, ascii art, ascii table, code ascii, ascii

 

[ishanwagmi]

Dear etaf,
The above formula allows special char if inserted in after typing allowed characters.
E.G.
[TABLE="width: 223"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]1[/TD]
[TD]asdfg[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]%%%%[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]asdfg%%[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1234%%%[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]%%%qwet[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]12345[/TD]
[TD="align: center"]TRUE
[/TD]
[/TR]
</tbody>[/TABLE]

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]

try

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

 

[ishanwagmi]

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></o>

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



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

 

[Rick Rothstein]

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></o>

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

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

 

[ishanwagmi]

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

 

[ishanwagmi]

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

Sorry, Just checked... app is available.

Thanks,
Ishan

 

[Momanda]

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!