Data I have Excel 2002 as part of Office XP Standard. I tried alidation, I think
Gareth,
To restrict it to an upper case "A" use:
=AND(LEN(A1)=8,LEFT(A1,1)="A",ISNUMBER(VALUE(RIGHT (A1,7)))
The previous formula allowed either upper or lower case.
--
sb
"Gareth" wrote in message
...
Tom and steve
it still allows me to enter a small 'a'
Gareth
"steve" wrote in message
...
Tom,
Thanks for pointing me to "ISNUMBER". I was getting ready to suggest
an event macro.
A further mod would be to use "VALUE" instead of "*1".
=AND(LEN(A1)=8,LOWER(LEFT(A1,1))="a",ISNUMBER(VALU E(RIGHT(A1,7)))
[watch word wrap]
--
sb
"Tom Ogilvy" wrote in message
...
Steve's suggestion worked for me. did you select A1 and then do
Data=Validation, select custom and paste in the formula?
Here is a modification to restrict the last 7 to numbers:
=AND(LEN(A1)=8,LOWER(LEFT(A1,1))="a",ISNUMBER(RIGH T(A1,7)*1))
--
Regards,
Tom Ogilvy
"Gareth" wrote in message
...
Steve
Cannot seem to get it to work, I can see what LEN does and what
LEFT(A1,1)
does but it doesn't work.
Also, is it possible to ensure that the last 7 characters are
numerical?
Gareth
"steve" wrote in message
...
Gareth,
For Data Validation (either manual or by code).
Set "Allow:" to "Custom"
In the "Formula:" box put
=AND(LEN(A1)=8,LOWER(LEFT(A1,1))="a")
(this is for cell A1, adjust as needed)
change to
=AND(LEN(A1)=8,A1="A")
to accept only capital A
In code it would look something like this
With Selection.Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop,
_
Operator:= _
xlBetween, Formula1:="=AND(LEN(E1)=8,E1:E1=""A"")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
[you can remove or amend most of the lines after the formula part]
hth
--
sb
"Gareth" wrote in message
...
I need to ensure that users only enter a customer number in the
correct
format in a cell.
The format is an 'A' followed by 7 numbers.
How can I use validation to ensure only this type of entry is
allowed?
Gareth
|