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?

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 DataValidation
Allow: Custom
Formula: =SUMPRODUCT(--(ISNUMBER(SEARCH(MyR,A1))))0
Click OK

Test it out ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
...
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?

On Apr 28, 10:16 am, wrote:
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

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


" wrote:

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 doesnt allows them to.

Can anyone help me please?

Oops, the earlier suggestion wasn't adequate. Dismiss that.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Go with Ken J's suggestion, which works fine.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

On Sun, 27 Apr 2008 17:16:30 -0700 (PDT), wrote:

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 procedu

<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

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",M ID(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

On Apr 28, 11:56*am, Ken Johnson wrote:
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",M ID(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!!