Data Validation

markster

Well-known Member
Joined
May 23, 2002
Messages
579
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello

Basically, I have column and I want people to enter letters with a comma after each letter so they might enter ABC they would be forced to enter it as A,B,C or CDEF forced to enter as C,D,E,F

I looked at data validation but can't see an option to do something like this. I'm sure there must be - any ideas?

Thanks,
Mark
 
First request option

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long
    Dim tmp1 As String, tmp2 As String
   
   
    Application.EnableEvents = False
   
    If Target.Column = 1 Then
        tmp1 = Replace(Target, " ", "")
               
        For i = 1 To Len(tmp1)
            If i <> Len(tmp1) Then
                If Mid(tmp1, i, 1) <> "," Then
                    tmp2 = tmp2 & Mid(tmp1, i, 1) & ","
                End If
            Else
                tmp2 = tmp2 & Mid(tmp1, i, 1)
            End If
        Next i
       
        While InStrRev(tmp2, ",") = Len(tmp2)
            tmp2 = Mid(tmp2, 1, Len(tmp2) - 1)
        Wend
       
        Target = tmp2
    End If
   
   
    Application.EnableEvents = True
End Sub
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Basically from post 4 onwards seems to work well enough for me - currently folks enter anything which they can no longer do. Thanks again to everyone.

@Fluff for 2nd request post 7 it's not so much what they enter so for instance it can either be X (a single number), X-X (a range) the key thing is that it forces the uses to enter a comma between entries in these formats. Does this make sense?

Thanks,
Mark
 
Upvote 0
As long as it's always a single character followed by a , or - you could try
Excel Formula:
=LET(s,SEQUENCE(LEN(F10)/2,,,2),AND(OR(MID(F10,s+1,1)=",",MID(F10,s+1,1)="-"),MID(F10,s,1)<>","))
but something like 1-12,3 will not be allowed.
 
Upvote 0
Thinking about it the formula should be
Excel Formula:
=LET(s,SEQUENCE(LEN(F10)/2,,,2),AND(OR(MID(F10,s+1,1)=",",MID(F10,s+1,1)="-"),AND(MID(F10,s,1)<>",",MID(F10,s,1)<>"-")))
although if you want only numbers every other character you could use
Excel Formula:
=LET(s,SEQUENCE(LEN(F10)/2,,,2),AND(ISERROR(MID(F10,s+1,1)+1),ISNUMBER(MID(F10,s,1)+0)))
 
Upvote 0
there needs to be a specific type of format this is different. For example they can enter a number range e.g. 1-5 or a single number such as 3 or a combination so they might enter 1-5,3,6-8
By single number, do you mean single digit or is this also a valid entry? "1-223,45,100,5-102"
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top