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

  • Thread starter Thread starter Gareth
  • Start date Start date
G

Gareth

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
 
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
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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(VALUE(RIGHT(A1,7)))

[watch word wrap]
--
sb
Tom Ogilvy said:
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(RIGHT(A1,7)*1))

--
Regards,
Tom Ogilvy

Gareth said:
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 said:
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
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
Click to expand...
Click to expand...
Click to expand...
 
Tom and steve

it still allows me to enter a small 'a'

Gareth

steve said:
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(VALUE(RIGHT(A1,7)))

[watch word wrap]
--
sb
Tom Ogilvy said:
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(RIGHT(A1,7)*1))

--
Regards,
Tom Ogilvy

Gareth said:
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
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
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
Tom and steve

it still allows me to enter a small 'a'

Gareth

steve said:
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(VALUE(RIGHT(A1,7)))

[watch word wrap]
--
sb
Tom Ogilvy said:
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(RIGHT(A1,7)*1))

--
Regards,
Tom Ogilvy

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
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
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
still allows a small 'a'!

steve said:
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 said:
Tom and steve

it still allows me to enter a small 'a'

Gareth

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(VALUE(RIGHT(A1,7)))

[watch word wrap]
--
sb
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(RIGHT(A1,7)*1))

--
Regards,
Tom Ogilvy

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
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,
Click to expand...
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
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
Click to expand...
Click to expand...
Click to expand...
 
steve

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

Gareth
steve said:
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 said:
still allows a small 'a'!

steve said:
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
Tom and steve

it still allows me to enter a small 'a'

Gareth

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(VALUE(RIGHT(A1,7)))

[watch word wrap]
--
sb
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(RIGHT(A1,7)*1))

--
Regards,
Tom Ogilvy

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
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top