Data Entry Mask

  • Thread starter Thread starter Ashish
  • Start date Start date
A

Ashish

I have a spreadsheet in which people are going to send me
information. One of the field that I want is the Sales
Order number. I want them to always use the format of
three alpha characters and seven numeric characters for
this field. Where should I create such a mask that they
can only enter the data that is like "AAA1234567" (three
alpha characters and seven numeric characters?
 
Hi
use 'Data - validation' and enter the following custom formula (e.g.
for cell A1):
=AND(SUMPRODUCT(--(ISNUMBER(--MID(A1,ROW(INDIRECT("1:3")),1))))=0,SUMPR
ODUCT(--(ISNUMBER(--MID(A1,ROW(INDIRECT("4:10")),1))))=7)
 
The Validation Rule errors out because it doesn't know
what range I'm referring to...it's saying that the range
specified is inaccurate.

Any other suggestions?

Ashish
 
Try this version of Frank's formula (you got hit by a line break):

=AND(SUMPRODUCT(--(ISNUMBER(--MID(A1,ROW(INDIRECT("1:3")),1))))=0,
SUMPRODUCT(--(ISNUMBER(--MID(A1,ROW(INDIRECT("4:10")),1))))=7)

Copy both physical lines (one logical line) and ctrl-v in the formula box (in
the data|validation dialog)
 

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

Number Assignment 3
Prevent alpha character from being entered into a numeric field 3
Help with MID Function? 20
SUMIF and multiple criteria 3
find and replace numeric strings in larger text strings 8
Ignore text but not numerics on SUM() function 1
How do I restrict entry into a cell to only alpha/numeric? 3
Lookup changing data range in external workbook 5

Back
Top