Hello,
Is it possible to format a cell to only allow numbers separated by comma then by one space like this:
125, 2368, 22, 33, 791, 698
Thank you,
Fred
Hello,
Is it possible to format a cell to only allow numbers separated by comma then by one space like this:
125, 2368, 22, 33, 791, 698
Thank you,
Fred
It would be data validation rather than formatting.
Here's an attempt that will detect non-numeric characters as well as commas without a following space. It's not foolproof - for example it will allow periods, but before we make the formula any more complex, let's see if this is a viable approach for you.
The formula as it stands (a) strips comma-space sequences and checks that the result is a valid number and (b) because random commas still pass the isnumber() test, a second test checks for any residual commas from the result of (a) and if any exist then the cell contents are declared invalid.
Assuming the cell that you want to check is A1:
Select cell A1
Data > Data Validation > Allow -> "custom"
Enter the formula:
Formula:Please Login or Register to view this content.
You may also want to set an error alert message: Something like: "Enter only numbers separated by comma-space"
Hopefully this helps.
Thanks for the reply. I tried the formula in data validation but it doesn't do anything for new entries. I think I might just put a rule attached by comment for that cell.
I'm not sure what you mean by "it doesn't do anything for new entries". Take a look at the attached workbook. Try entering invalidly formatted data into cell A2. You will see a popup saying "Bad format".
Let me know how you get on.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks