Data I have Excel 2002 as part of Office XP Standard. I tried alidation, I think
 

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

Data I have Excel 2002 as part of Office XP Standard. I tried alidation, I think
 

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

Data I have Excel 2002 as part of Office XP Standard. I tried alidation, I think
 

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

Data I have Excel 2002 as part of Office XP Standard. I tried alidation, I think
 

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

Data I have Excel 2002 as part of Office XP Standard. I tried alidation, I think
 

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

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

Data I have Excel 2002 as part of Office XP Standard. I tried alidation, I think
 

Gareth,

This will ensure a capital A
=AND(LEN(A1)=8,CODE(A1)=65,ISNUMBER(VALUE(RIGHT(A1 ,7))))

[watch for word wrap]

Sorry for my error! I thought that LEFT(A1,1)="A" was case sensitive.
Now I know better...

--
sb
"Gareth" wrote in message
...
still allows a small 'a'!

"steve" wrote in message
...
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

Data I have Excel 2002 as part of Office XP Standard. I tried alidation, I think
 

steve

many, many thanks for your help over the last couple of days.

Gareth
"steve" wrote in message
...
Gareth,

This will ensure a capital A
=AND(LEN(A1)=8,CODE(A1)=65,ISNUMBER(VALUE(RIGHT(A1 ,7))))

[watch for word wrap]

Sorry for my error! I thought that LEFT(A1,1)="A" was case sensitive.
Now I know better...

--
sb
"Gareth" wrote in message
...
still allows a small 'a'!

"steve" wrote in message
...
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