Data Validation: Only allow comma or space(Alt+Enter) & Integer

[dellzy]

Hi,

I have a column for Case Number which is supposed to be integer only. But for some reasons, user sometimes need to enter more than 1 case number in a cell. At first, I set a rule in data validation to only allow integer to avoid user entering strings (unintentionally) but it becomes troublesome for the user when they just want to enter comma or at least press Alt+Enter to allow next line because the rule treats these as strings and restrict the entry. We cannot ask user to enter the extra case numbers in separate rows individually because it must be counted as 1 record of row when we do the pivot table for reporting purposes.

Is there any workaround, please?

Appreciate some help.

Thank you in advance.

DZ

 

[konew1]

You can set up a data validation formula like this one, for numbers separated by comma.
=ISNUMBER(--SUBSTITUTE(A1,",",""))

 

[dellzy]

Hi konew1,

You can set up a data validation formula like this one, for numbers separated by comma.
=ISNUMBER(--SUBSTITUTE(A1,",",""))

Tq! It works! But can I enhance it to allow only max 8 digits for the case number before next comma, please?

Tq in advance.

DZ

 

[konew1]

Here is one step towards where you want to be, but it has limitations.
=AND(ISNUMBER(--SUBSTITUTE(A1,",","")),IF(LEN(A1)>8,SEARCH(",",A1)< =9,FALSE))
It will only check the location of the first comma being < =9. It does not check the length of the second or subsequent case numbers.

If all case numbers must be exactly 8 digits then you could use MOD function, but that would allow one case number of 7 digits followed by another of 9 digits