Data Validation

  • Thread starter Thread starter Hawk
  • Start date Start date
H

Hawk

I would like to limit the user to entering text as follows
into a cell:

text/text

for example:

kg/mL
IU/mL
g/cm^3

I can do this using VBA, but I was wondering if there is
anyway to do this using Excel's data validation feature?
TIA...
 
Hawk,

For cell A1, use the Custom setting , with a formula of (all on one row, so
watch line wrapping)

=AND(NOT(ISERROR(FIND("/",A1))),
ISERROR(VALUE(LEFT(A1,FIND("/",A1)-1))),
ISERROR(VALUE(MID(A1,FIND("/",A1)+1,LEN(A1)))))

HTH,
Bernie
MS Excel MVP
 
Hi Hawk
Enter the following formula as your validation

=AND(LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))=1,LEFT(A1,1)<>"/",RIGHT(A1,1)<>"/"

The first componet checks to make sure there is only one "/" in the text. The second and third components makes sure the "/" isn't the first or last character. Adjust the A1 reference to your cell reference

Good Luck
Mark Graesse
(e-mail address removed)
Boston MA

----- Hawk wrote: ----

I would like to limit the user to entering text as follows
into a cell

text/tex

for example

kg/m
IU/m
g/cm^

I can do this using VBA, but I was wondering if there is
anyway to do this using Excel's data validation feature?
TIA..
 
Back
Top