• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Force specific entry pattern using data validation

Wanderingpuppy

New Member
I’m needing a data validation formula for specific entries to a cell. I’m looking to have them not proceed til the entry fits the pattern exactly.

The pattern the entry into the cell must follow is ##-###|###.

Two digit number, hyphen, three digit number, bar, three digit number.

Any help is appreciated!!
 
Last edited:
I’m needing a data validation formula for specific entries to a cell. I’m looking to have them not proceed til the entry fits the pattern exactly.

The pattern the entry into the cell must follow is ##-###|###.

Two digit number, hyphen, three digit number, bar, three digit number.

Any help is appreciated!!

I think, set the cell format to custom ##-###|### and then you can set a data validation for text length equal to 8 and type values without symbols
 
I think, set the cell format to custom ##-###|### and then you can set a data validation for text length equal to 8 and type values without symbols

Doing it that way still allows them to put in something like 11-111-111 and doesn't force them to fix it.

Can I combine the data validation formula to look for the hyphen and Bar and also restrict the length?
 
Doing it that way still allows them to put in something like 11-111-111 and doesn't force them to fix it.

Can I combine the data validation formula to look for the hyphen and Bar and also restrict the length?
It doesn't allow to type any symbols if the maximum length is set as 8. Here 11-111-111 the total text length is 10 including symbols.
Format cells just changes the display and the actual value in the cell will be 11111111 only.
You can try custom validation like =AND(MID(A1,3,1)="-",MID(A1,7,1)="|",LEN(A1)=10)
Please note - Data validation doesn't work if you copy ay other cell and paste it to a cell with data validation.
 
Last edited:
It doesn't allow to type any symbols if the maximum length is set as 8. Here 11-111-111 the total text length is 10 including symbols.
Format cells just changes the display and the actual value in the cell will be 11111111 only.
You can try custom validation like =AND(MID(A1,3,1)="-",MID(A1,7,1)="|",LEN(A1)=10)
Please note - Data validation doesn't work if you copy ay other cell and paste it to a cell with data validation.

That Formula works! Thank you and thank you for the info on the copying into the data validation cell. The cells I'm using the data validation on are currently linked to other cells, so they show a 0 when there is no entry into the source cell. The data validation circles the single zero's as invalid even with ignore blanks turned on. Is there something that can be added onto that formula that will exclude a single zero so it doesn't circle them as invalid?
 
That Formula works! Thank you and thank you for the info on the copying into the data validation cell. The cells I'm using the data validation on are currently linked to other cells, so they show a 0 when there is no entry into the source cell. The data validation circles the single zero's as invalid even with ignore blanks turned on. Is there something that can be added onto that formula that will exclude a single zero so it doesn't circle them as invalid?
I'm not clear in the absence of a sample file, are you talking about conditional formatting ?
You may try something like this in the cell where it is linked to=IF(ISBLANK(A1),"",A1)
 
Back
Top