Data Validation

[markster]

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

 

[JamesCanale]

You could process whatever they put in - like this:

Mr Excel Playground 3.xlsm
	J
13	ABC
14	A,B,C
Sheet17

Cell Formulas
Range	Formula
J14	J14	=TEXTJOIN(",",TRUE,MID(J13,SEQUENCE(LEN(J13)),1))

 

[markster]

Thanks Mate

 

[Fluff]

You can use data validation & use Custom with this formula

=LEN(F10)-LEN(SUBSTITUTE(F10,",",""))+1=INT(LEN(F10)/2)+1

Change the F10 to the cell with the DV

 

[Peter_SSs]

You can use data validation & use Custom with this formula

=LEN(F10)-LEN(SUBSTITUTE(F10,",",""))+1=INT(LEN(F10)/2)+1

Change the F10 to the cell with the DV

That would allow, for example "A,,C" or "AC,"

 

[Fluff]

That's a very good point. Hadn't thought of that.

 

[markster]

Perfect thanks guys - also I have another one where 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 once again I what ever they enter needs to have a comma between entries as shown - could you help with this one too? Thanks

 

[Peter_SSs]

Perfect thanks guys

Which one? Neither seems to me to do what you asked - refer to my example in post #5.

 

[JamesCanale]

I don't think that the computer can know the difference in intent between "1-5,3,6-8" and "1-53,6-8".

 

[Fluff]

Whilst still not perfect, that is better than my 1st attempt.

=LET(s,SEQUENCE(LEN(F10)/2,,,2),AND(MID(F10,s+1,1)=",",MID(F10,s,1)<>","))

I can see no way of doing your 2nd request.