Data validation and numbers + hyphens

Jordytee

New Member
Joined
Nov 8, 2018
Messages
6
Hi there all, i want to set a data validation rule so that only the following number sequence can be entered to a cell: 000000-00
the hyphen is a must, if i didnt need the hyphen I would be fine setting this up. Any ideas?

I use the data validation from the ribbon, and need a custom formula
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi
Welocome to the board

For ex., for B2:

=AND(LEN(B2)=9,ISNUMBER(-MID(B2,ROW(INDIRECT("1:6")),1)),MID(B2,7,1)="-",ISNUMBER(-MID(B2,8,1)),ISNUMBER(-MID(B2,9,1)))
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I think this will work:
=AND(LEN(REPLACE(B2,7,1,""))=8,MID(B2,7,1)="-",ISNUMBER(SUBSTITUTE(B2,"-","")+0))
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

Hi Scott

I think that will work for most of the values that the user might try, but to be sure that the pattern is respected I believe you have to check each character separately.

Your formula accepts:

1,2345-67
12.345-67
12345-67 (space before)
123456-7 (space after)
2019-1--1
10:21:-23
123456-e2
12345e-+3
+1234-56 (space before)
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,051

ADVERTISEMENT

How about

=AND(VALUE(1&LEFT(F3,6))>=100000, MID(F3,7,1)="-", VALUE(1&RIGHT(F3,2))>=100)
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi Mike

In the case of your formula you can trick Value() with the exponential

Try:

123456-e7
1.23e8-11


Like in Scott's case, maybe not really important, but always good to know.
 
Last edited:

Forum statistics

Threads
1,136,260
Messages
5,674,681
Members
419,520
Latest member
Jennifer4Dillon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top