Data Validation: 11 digits (numbers) in a merged cell
Hi,
I want to apply Data Validation to 11 digits (numbers) in a merged cell (F4:M4).
I have tried Data Validation>Text Length>Equal To> =AND(ISNUMBER(F$4),LEN(F$4)=11) but it does not work.
Also tried using Data Validation>Custom & then same formula, does not work.
There should be 11 digits starting with 0 & all should be numbers.
Please help.
1. Why are you merging cells. Just use 1 and widen it.
2. Leading zeros will be dropped unless the cell is pre-formatted as TEXT.
3. Use this:
=AND(ISNUMBER(F4+0),LEN(F4)=11,LEFT(F4,1)="0")
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Set the data validation to whole number - between 0 and 99999999999 (11 lots of 9).
Set custom formatting for the cell - 00000000000 (11 lots of 0).
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi Glenn,Originally Posted by Glenn Kennedy
Thanks. It worked.
Hi AliGW,
I actually knew these tips but wanted a formula to restrict user to enter exactly 11 numbers & show an error message in case of wrong input which I thought was not possible without Data Validation.
Anyway, the formula given by Glenn worked.
Thanks for your time.
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
So if you knew this already, why didn't you say you didn't want to do it that way?
I can't honestly see that it makes one scrap of difference which approach you use. Both use data validation.
Ali> Your formula would permit entry of a 10 digit number. It would simply add an extra leading zero.
That's true, I suppose.
Hi AliGW,
I again appreciate your help.
But in your process if I enter 10 or lower number of digits as a mistake, it still converts it to an 11 digit number by adding a 0 prior to it.
Hope you understand.
Thanks.
See post #9.
Give me the link Pls.
Thanks.
The link? Just look up to the post above your last one!!!
What link are you talking about???
Sorry, both of you guys.
I have got it.
Thanks.
No worries.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks