Hi All,
I'm new to this forum. I was hoping someone can help me with an issue i'm having.
I'm trying to make a column where users can input an account number. This account number has 3 (or sometimes 4 or 5) leading zeros. All account numbers are exactly 10 numbers long and the zeros must be visible in the cell. I'd like the data validation to stop anything else typed in if it does not follow these rules. (ex. 0001234567). I have excel 2010. Thanks.
A combobox from the controls tool bar would work better as it has autocomplete
Hi,
Thank you for your reply. Is there anyway to do this with Data Validation? Essentially all I need is the cell to only allow users to input numbers that are exactly 10 digits long and to show any leading zeros.
Thanks,
You will have to preformat the target cell(s) as TEXT to allow leading zeros.
Data Validation
Allow: Custom
Formula:
=AND(LEN(A1)=10,COUNT(-MID(A1,ROW(INDIRECT("1:10")),1))=10)
If you test that formula on the worksheet it must be array entered.
Array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
,
Bookmarks