Data Validation - Allow numbers, comma, - and space

[Ansher.M]

Hello

I am trying to have data validation for a cell which only allows users
to enter numbers [0-9] , comma (,) , hyphen (-) and space. User should
be able to enter a combination of these characters.

For Eg: 3, 45-60

I have used this formula
=OR(F8=" ",F8="-",F8=",",F8="0",F8="1",F8="2").

But the problem with this is it allows as long as user enters any one
of these character, if they enter multiple it doesn’t allows them to.

Can anyone help me please?

 

[Max]

Try this play ..

In Sheet1,
List in say, E1:E13 the 13 allowed characters: 0-9, comma, hyphen, space
Then define a range: MyR =Sheet1!$E$1:$E$13

To effect the validation
Assume data entry in A1 down
Select col A (A1 active)
Click Data>Validation
Allow: Custom
Formula: =SUMPRODUCT(--(ISNUMBER(SEARCH(MyR,A1))))>0
Click OK

Test it out ..

---
Hello

I am trying to have data validation for a cell which only allows users
to enter numbers [0-9] , comma (,) , hyphen (-) and space. User should
be able to enter a combination of these characters.

For Eg: 3, 45-60

I have used this formula
=OR(F8=" ",F8="-",F8=",",F8="0",F8="1",F8="2").

But the problem with this is it allows as long as user enters any one
of these character, if they enter multiple it doesn’t allows them to.

Can anyone help me please?

 

[Ken Johnson]

Hello

I am trying to have data validation for a cell which only allows users
to enter numbers [0-9] , comma (,) , hyphen (-) and space. User should
be able to enter a combination of these characters.

For Eg: 3, 45-60

I have used this formula
=OR(F8=" ",F8="-",F8=",",F8="0",F8="1",F8="2").

But the problem with this is it allows as long as user enters any one
of these character, if they enter multiple it doesn’t allows them to.

Can anyone help me please?

One way...

paste this User Defined Function into a standard code module in the
workbook...

Public Function IsValid(str As String) As Boolean
Dim Char As Long
For Char = 1 To Len(str)
Select Case Mid(str, Char, 1)
Case " ", ",", "-", _
"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"
IsValid = True
Case Else
IsValid = False
Exit Function
End Select
Next Char
End Function

Test the validity of F8 by typing...

=IsValid(F8)

into a spare cell on the sheet, say F7.

Use =F7 (or whatever) as your Custom Formula in the data validation.

Ken Johnson

 

[Don]

Someone else may have a better answer , but the Search function can find char
within a cell and return the position.

I also like the answer on another sheet , put all the options and define
that list as "list1" then do a data validate / list as =list1

 

[Max]

Oops, the earlier suggestion wasn't adequate. Dismiss that.

---

 

[Max]

Go with Ken J's suggestion, which works fine.

---

 

[Ron Rosenfeld]

Hello

I am trying to have data validation for a cell which only allows users
to enter numbers [0-9] , comma (,) , hyphen (-) and space. User should
be able to enter a combination of these characters.

For Eg: 3, 45-60

I have used this formula
=OR(F8=" ",F8="-",F8=",",F8="0",F8="1",F8="2").

But the problem with this is it allows as long as user enters any one
of these character, if they enter multiple it doesn’t allows them to.

Can anyone help me please?

Assume you want to enter data into F8.

First, enter this UDF using the following procedure:

<alt-F11> opens the VB Editor.
Ensure your project is highlighted in the project explorer window. Then
Insert/Module and paste the code below into the window that opens.

In some cell on the same worksheet, e.g. AA8, enter the formula =Valid(F8)

In F8, use the data validation formula =AA8

=============================
Option Explicit
Function Valid(str) As Boolean
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "^[\d\s,-]+$"
Valid = re.Test(str)
End Function
==========================

--ron

 

[Ken Johnson]

Hi,

If A UDF is not allowed then the following seems to work…

F8 on Sheet1 needing validation. On a spare sheet (I used Sheet3) in
A1 enter a string consisting of all the allowable characters. In this
case…

", -1234567890"

in A2 on the spare sheet enter the following formula...

=IF(MID(Sheet1!$F$8,ROWS($A$2:$A2),1)="","BLANK",MID(Sheet1!$F
$8,ROWS($A$2:$A2),1))

In B2 on the spare sheet enter this formula...

=IF(A2="BLANK",0,IF(ISERROR(FIND(A2,$A$1)),0,--(FIND(A2,$A$1)>0)))

Fill the formulas in A2 and B2 down so that the number of rows with
the formulas is not less than the number of characters in the longest
possible string to be entered into F8. (I filled down to row 31).

In B1 on the spare sheet enter the following formula...

=IF(SUM(B2:B31)=LEN(Sheet1!F8),TRUE,FALSE)

With B1 selected go Insert|Name|Define... then type
"IsF8Valid" (without the speech marks) into the Name box. Click Add
then OK.

Select F8 then apply the data validation using "=IsF8Valid" (without
the speech marks) as the Custom Formula.

Ken Johnson

 

[Ansher]

Hi,

If A UDF is not allowed then the following seems to work…

F8 on Sheet1 needing validation. On a spare sheet (I used Sheet3) in
A1 enter a string consisting of all the allowable characters. In this
case…

", -1234567890"

in A2 on the spare sheet enter the following formula...

=IF(MID(Sheet1!$F$8,ROWS($A$2:$A2),1)="","BLANK",MID(Sheet1!$F
$8,ROWS($A$2:$A2),1))

In B2 on the spare sheet enter this formula...

=IF(A2="BLANK",0,IF(ISERROR(FIND(A2,$A$1)),0,--(FIND(A2,$A$1)>0)))

Fill the formulas in A2 and B2 down so that the number of rows with
the formulas is not less than the number of characters in the longest
possible string to be entered into F8. (I filled down to row 31).

In B1 on the spare sheet enter the following formula...

=IF(SUM(B2:B31)=LEN(Sheet1!F8),TRUE,FALSE)

With B1 selected go Insert|Name|Define... then type
"IsF8Valid" (without the speech marks) into the Name box. Click Add
then OK.

Select F8 then apply the data validation using "=IsF8Valid" (without
the speech marks) as the Custom Formula.

Ken Johnson

Hello

Thanks everyone for your help ... Solution given by Ken J UDF works
perfect!!