Alphanumeric Characters only - data validation

[pradipto]

Hi,

I want to do data verification. Only Alphanumeric characters are allowed with the following form:

1) Total length = 12
2) First 8 characters = Alphanumeric
3) Last 4 characters = Numbers

Example: srdzzapp0309
srpzzapp0310
c1dafora0001
c2dafora0001

Can you please help

[Kaper]

Hi,
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Glenn Kennedy]

Hi. Assuming data start in A1, you can use this formula:

IF(AND(LEN(A1)=12,AND(CODE(UPPER(MID(A1,{"1","2","3","4","5","6","7","8"},1)))>=65,CODE(UPPER(MID(A1,{"1","2","3","4","5","6","7","8"},1)))<=90)=TRUE,SUM(IF(ISNUMBER(--MID(A1,{"9","10","11","12"},1)),1,0))=4),TRUE,FALSE)

[pradipto]

Thanks. This Works.

Can the data validation be done on existing values given in the excel. Something like the cell background will become red if it doesn't match the data validation criterion.

[Glenn Kennedy]

Excel doesn't like arrays - the bits inside the {} - in CF. So this is the nearest I can reasonably get... Apply the formatting to whatever range you need. Currently applied from A1 to B20

[ChemistB]

Good point about the arrays. FYI, your first formula could be simplified significantly. No need for the quotes around the 1,2,3... and some of the extra AND's and the last 4 digits can be tested as 1.

=AND(LEN(A1)=12,CODE(UPPER(MID(A1,{1,2,3,4,5,6,7,8},1)))>=65,CODE(UPPER(MID(A1,{"1","2","3","4","5","6","7","8"},1)))<=90,ISNUMBER(--RIGHT(A1,4)))

[Glenn Kennedy]

Good point about the end number...

[AlKey]

I think this could work too

Please Login or Register  to view this content.

v	A	B
1	srdzzapp0309	TRUE
2	srpzzapp0310	TRUE
3	c1dafora0001	FALSE
4	c2dafora0001	FALSE
5	1234asdfghjk	FALSE

[Glenn Kennedy]

Alkey... I have no idea how that comes up with an answer... But

A*CDEFGH1234

doesn't work

[Glenn Kennedy]

Alkey... I have no idea how that comes up with an answer... But

A*CDEFGH1234

doesn't work

[Glenn Kennedy]

So, pradipto. after some discussions, this is probably the best to date...

[AlKey]

Glenn,
The OP's requirement is dealing with numerical/alphanumerical values. Asterisk is neither of them. It is an operator. I don't see the point of introduction of a foreign object that clearly doesn't belong there.

[ChemistB]

The OP's requirement is to ALLOW only alphanumerical values.

So shouldn't c1dafora0001 and C2dafora0001 be true also? Glenn's array (and my simplification) only looks at the first value in the 1,2,3,4,5,6,7,8 sequence.

[Glenn Kennedy]

Incidentally... chaps. Have we ALL misread this?? The OPs reply at Post 4, to my first shot, suggests otherwise... but look at the EXACT wording of Post 1....

[Glenn Kennedy]

Pradipto, is 123456781234 a valid entry?

[pradipto]

No 123456781234 is not a valid entry.

1) Total length = 12
2) First 8 characters = Alphanumeric
3) Last 4 characters = Numbers

Example: srdzzapp0309
srpzzapp0310
c1dafora0001
c2dafora0001
C3dafora8765

[Glenn Kennedy]

You have just repeated yourself, not provided any clarification.

Of the 5 exampes you posted:
srdzzapp0309
srpzzapp0310
c1dafora0001
c2dafora0001
C3dafora8765

Which ones are OK. If ALL are OK, why is 123456781234 NOT acceptable?
If only the first 2 are OK, then look back at post 11. Does this do/not do what you want. If it DOES NOT do what you want, please explain why.

MORE EXPLANATION please.....

[pradipto]

Hi,

May be my explanation was not adequately clear:

1) Total length = 12
2) First 8 characters = Alphanumeric(Atleast one character must be present)
3) Last 4 characters = Only Numbers

123456781234 is not ok..because of condition 2)

[samba_ravi]

=AND(LEN(A1)=12,COUNT(RIGHT(A1,4)+0),ISERROR(LEFT(A1,8)+0),LOOKUP(2,1/(MID(A1,ROW(INDIRECT("1:8")),1)+1),MID(A1,ROW(INDIRECT("1:8")),1)+1))
Try above formula in Data Validation

[AlKey]

@pradipto
What makes your explanation confusing is use of word "Alphanumeric".The term Alphanumeric in regard to examples provided, mean that all strings contain numbers and letters are Alphanumeric.

https://en.wikipedia.org/wiki/Alphanumeric

Now, which of these suggested strings you consider "valid"?
srdzzapp0309
srpzzapp0310
c1dafora0001
c2dafora0001
C3dafora8765