Hi Smarter than me People,
I am looking for a way to apply data validation to cells, but limit the cell input to 12 digit hexadecimal, i.e., 0-9,A-F.
In other words when the data entry is done for these cells, the only thing that can be entered is a 12 digit HEX
I have tried some data validation with text, different variations of cell format, but nothing really seems to satisfy what I am looking for.
Any insight and help, as usual, is greatly appreciated.
tks,
Tim
Hi Tim,
I think you need a Custom Validation rule on those cells that looks like this:
=AND(LEN(A2)<13,ISERROR(HEX2DEC(A2))=FALSE)
See the attached for the example.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Marvin, Hex2Dec only works for up to 10 characters in Excel 2010. Have they extended it for 2013?
Entia non sunt multiplicanda sine necessitate
I tried this, but it does appear to not work for anything larger than 10 characters. (I am using Excel 2010, btw)
How about ...
=AND(LEN(A2) <= 12, ISNUMBER(HEX2DEC(LEFT(A2, 10)) + HEX2DEC(MID(A2, 11, 2))))
Thanks shg, that does exactly what I was looking for.
tks,
You're welcome. Just a minor change to Marvin's suggestion.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks