SOLVED

Help with Custom cell format

%3CLINGO-SUB%20id%3D%22lingo-sub-1316004%22%20slang%3D%22en-US%22%3EHelp%20with%20Custom%20cell%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1316004%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20there!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20limit%20what%20can%20be%20inserted%20in%20an%20excel%20cell.%20I%20want%20to%20limit%20the%20options%20to%20one%20or%20more%20six%20digit%20numbers%20separated%20by%20a%20comma%20and%20a%20space.%3C%2FP%3E%3CP%3EExamples%20of%20allowed%20entries%3A%3C%2FP%3E%3CP%3E123456%3C%2FP%3E%3CP%3E123456%2C%20123456%3C%2FP%3E%3CP%3E123456%2C%20123456%2C%20123456%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExamples%20of%20forbidden%20entries%3C%2FP%3E%3CP%3E12345%3C%2FP%3E%3CP%3E123456%3B%20123456%3C%2FP%3E%3CP%3E123456%20123456%3C%2FP%3E%3CP%3E123456%20abc%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20struggling%20to%20find%20the%20correct%20custom%20format%20for%20this.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1316004%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1318478%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Custom%20cell%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1318478%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F626325%22%20target%3D%22_blank%22%3E%40Claudia350%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20general%2C%20what%20you're%20trying%20to%20do%20doesn't%20make%20sense%20from%20the%20perspective%20of%20sound%20spreadsheet%20design.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(A%20basic%20concept%20in%20spreadsheets%20and%20databases%20is%20that%20of%20%22data%20integrity%22%20Data%20integrity%20is%20what%20lies%20behind%20Excel's%20data%20validation--which%20is%20where%20I%20assume%20you're%20trying%20to%20do%20this%20in%20the%20first%20place--in%20that%20its%20goal%20is%20to%20limit%20entries%20to%20a%20certain%20format%20or%20set%20of%20values.%20The%20underlying%20reason%20for%20that%20is%20that%20you%20don't%20want%20to%20have%20the%20classic%20data%20processing%20problem%20of%20GIGO%2C%20%22Garbage%20In%2C%20Garbage%20Out%22%26nbsp%3B%26nbsp%3B%26nbsp%3B%20You%20don't%20want%20people%20to%20enter%20something%20which%20would%20go%20through%20whatever%20downstream%20processes%20you%20have%20in%20mind%2C%20produce%20a%20result%2C%20and%20have%20that%20result%20be%20accepted%20when%20it%20shouldn't%20have%20ever%20gotten%20there.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%2C%20by%20effectively%20muddying%20the%20waters%20here%20with%20what%20really%20are%20multiple%20%22acceptable%20criteria%22%20so%20far%20as%20the%20computer%20is%20concerned--they%20look%20distinct%20to%20you%20and%20me%2C%20because%20we%20are%20human%20and%20can%20see%20beneath%20the%20differences%20to%20a%20commonality--even%20if%20you%20and%20we%20could%20come%20up%20with%20a%20way%20to%20satisfy%20your%20original%20query%2C%20you%20might%20be%20opening%20yourself%20up%20to%20GIGO.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBetter%20would%20be%20to%20accept%20only%20six%20digit%20entries.%20Full%20Stop.%26nbsp%3B%20Perhaps%20have%20multiple%20rows%20of%20data%2C%20one%20for%20each%20entry%20of%20six%20digits...but%20kept%20distinct%20as%20entries.%20In%20the%20data%20validation%20screen%20there%20are%20two%20ways%20I%20see%20that%20you%20could%20allow%20only%20six%20digit%20entries.%20IF%20what%20you're%20looking%20for%20are%20numbers%20on%20which%20you're%20going%20to%20perform%20arithmetical%20processes%2C%20you%20can%20select%20%22Whole%20Number%22%20and%20then%20specify%20that%20it%20has%20to%20be%20between%201000000%20and%20999999.%20If%2C%20on%20the%20other%20hand%2C%20you're%20talking%20of%20serial%20numbers%20or%20the%20like%2C%20where%20it's%20really%20for%20all%20practical%20purposes%20text%2C%20then%20just%20select%20the%20entry%20that%20says%20%22Text%20Length%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1587234998619.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F185176iA34C34C1C6A2EB46%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22mathetes_0-1587234998619.png%22%20alt%3D%22mathetes_0-1587234998619.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1347522%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Custom%20cell%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1347522%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20the%20help%2C%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hey there!

 

I'm trying to limit what can be inserted in an excel cell. I want to limit the options to one or more six digit numbers separated by a comma and a space.

Examples of allowed entries:

123456

123456, 123456

123456, 123456, 123456

 

Examples of forbidden entries

12345

123456; 123456

123456 123456

123456 abc

 

I'm struggling to find the correct custom format for this.

2 Replies
Highlighted
Best Response confirmed by Claudia350 (Occasional Contributor)
Solution

@Claudia350 

 

In general, what you're trying to do doesn't make sense from the perspective of sound spreadsheet design.

 

(A basic concept in spreadsheets and databases is that of "data integrity" Data integrity is what lies behind Excel's data validation--which is where I assume you're trying to do this in the first place--in that its goal is to limit entries to a certain format or set of values. The underlying reason for that is that you don't want to have the classic data processing problem of GIGO, "Garbage In, Garbage Out"    You don't want people to enter something which would go through whatever downstream processes you have in mind, produce a result, and have that result be accepted when it shouldn't have ever gotten there.)

 

Now, by effectively muddying the waters here with what really are multiple "acceptable criteria" so far as the computer is concerned--they look distinct to you and me, because we are human and can see beneath the differences to a commonality--even if you and we could come up with a way to satisfy your original query, you might be opening yourself up to GIGO.

 

Better would be to accept only six digit entries. Full Stop.  Perhaps have multiple rows of data, one for each entry of six digits...but kept distinct as entries. In the data validation screen there are two ways I see that you could allow only six digit entries. IF what you're looking for are numbers on which you're going to perform arithmetical processes, you can select "Whole Number" and then specify that it has to be between 1000000 and 999999. If, on the other hand, you're talking of serial numbers or the like, where it's really for all practical purposes text, then just select the entry that says "Text Length"

 

mathetes_0-1587234998619.png

 

Highlighted

Thank you for the help, @mathetes