2 Part Formula

A

ashley

I need a formula that, once locked, the cells in Column A will only accept
uppercase B's or S's and nothing else (no other letters, numbers, characters,
etc.). HELP!?
 
P

PCLIVE

You could use Data-Validation.
Allow:
List
Source:
B,S

That should do what you want.

HTH,
Paul
 
T

Teethless mama

Data Validation > Allow: Custom > Formula:
=AND(OR(CODE(A1)=66,CODE(A1)=83),LEN(A1)=1)
 
P

Peo Sjoblom

You are using B's and S's does that mean you can allow for instance BS or
BBBB or SSSS etc?
 
M

Mike H

Ashley,

For multiple Bs & Ss I had to resort to a macro:-

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
For x = 1 To Len(Target.Value)
If Mid(Target.Value, x, 1) = "B" Or Mid(Target.Value, x, 1) =
"S" Then
Else
MsgBox ("Illegal entry, Capital B or S only")
Target.Value = ""
End If
Next
End If
End Sub

Mike
 
E

Elkar

Here's a Custom Data Validation Formula that can allow for multiple B's and
S's.

=LEN(SUBSTITUTE(SUBSTITUTE(A1,"B",""),"S",""))=0

HTH,
Elkar
 
P

Peo Sjoblom

Nice


Peo


Elkar said:
Here's a Custom Data Validation Formula that can allow for multiple B's
and
S's.

=LEN(SUBSTITUTE(SUBSTITUTE(A1,"B",""),"S",""))=0

HTH,
Elkar
Click to expand...
 
Top