Data validation: specifying a text format

  • Thread starter Thread starter Hall
  • Start date Start date
H

Hall

I want to limit a cell's input to a set format 9999-999, where a 9 is a
digit. Is this possible?
 
Hi!

If I understand what you want, try this:

Select the cell(s) you want to validate
Goto Data>Validation
Allow: Custom
Formula:

=AND(ISNUMBER(SUBSTITUTE(A1,"-","")+0),A1=TEXT(A1,"0000-000"))

Biff
 
Ooops!

Caught a bug.
=AND(ISNUMBER(SUBSTITUTE(A1,"-","")+0),A1=TEXT(A1,"0000-000"))
Click to expand...

Change to:

=AND(ISNUMBER(SUBSTITUTE(A1,"-","")+0),A1=TEXT(A1,"0000-000"),LEN(A1)=8)

Biff
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

view format problem 2
Input of phone numbers 3
Excel Negative Time Format 8
Is there a quick way to format a phone feild in a form 3
Change number format 5
how do i change phone number formats in outlook 2003? 6
text search for a non-constant value? 14
phone formatting 2

Back
Top