Allow only 10 digit numeric mobile number in Excel using Data validation

You want a column as 'mobile numbers' in your data and want to ensure only 10 digits and numeric values are entered. It shouldn't allow text values.
Use this Custom Data Validation technique to ensure your requirement is met. It uses a combination of LEN and ISNUMBER functions.
Play around with this method, to make your own Custom formula too!
For 1 more custom data validation technique, see
• Custom Data Validation...
For more videos to learn excel, click this link
• Learn Excel's Tricks
SUBSCRIBE!
/ @excelrush
Blog
Visit www.ExcelRush.com for the free blog to learn Excel in-depth.
Rushabh Shah is an expert in training Professionals & Students in Excel's Advanced features & formulas. These tutorials are simply a way to spread the knowledge and make people more productive using Excel.
For Classroom & Online Live Trainings with Rushabh, visit www.DLTC.co

[@Santosh-jx3yf]

Very Nice I am searching the same

[@emiliomarrello7161]

for North America thinking we use 123 456 7869 right-click, format cells, and number custom formatting works awesome. BUT how do I protect just his format for this cell so that my team does not use copy and paste and wipe out formatting? I want to protect data format but not lock out field for input

[@Excelrush]

kzbin.info/www/bejne/hYual4FsnZijiNk This video has all that you're looking for :-)

[@ManojGupta-nl8fj]

Sir main ISI prakar ki tric ki Khoj mein tha bahut bahut dhanyvad iske liye

[@saivinodh1775]

How to extract mobile no from each row and copy to the last cell in that row.

[@deepakbhanushali1]

if have applied this function to check for duplicate and text charactor no > 30 =AND(LEN(a2)

[@deepakbhanushali1]

I got the formula actually removed inbetween AND Function and there it is

[@rokonuddin295]

How to Avoid Duplication Number ?

[@rashedict4592]

Please Add countif

[@xMazoJr]

What if the first digit is a 0? what do you have to do? apperently when i put 0 in the beginning, it does not count as a digit.

[@Excelrush]

while entering the number that starts with a 0, put a ' first. Example, '0123456789. It will accept it. if you like this solution, do subscribe to the channel!

[@simonkirkebyrasmussen6124]

@@Excelrush That does not work, since it then treats the input as text. Do you have a work-around where you can input a 0 (zero) as the first digit?

[@Excelrush]

No, there's no other way that I know of.

[@simonkirkebyrasmussen6124]

ok, thank you

[@parthvaghela7389]

It's work in Google sheets?

[@Excelrush]

Yes, it will work in Google sheets

[@parthvaghela7389]

Sir i need atomatic capital latter on first latter of word whan i type word i need to know aa formula for thet in Google sheets...

[@sathishsunshine]

hi sir i love your videos. i have a question how to to alphabet and number total fixe by 20 nos only accept how to to

[@Excelrush]

Data tab - Data validation - text length, less than equal to 20

[@sathishsunshine]

@@Excelrush this the example OD126544195465659000 two text and 18 number this type should be validate as to be good. not gose extra not less than. please tell me

[@kunalbajaj3547]

i forgot my number i know the last two digit ********82 how to find 1st eight number

[@Excelrush]

Hi Kunal, I didn't quite understand the question, can you please elaborate more?

[@kunalbajaj3547]

@@Excelrush i forgot the number 1st eight digit how to find this is Aircel number & aircel company already shutdown the number is ********82 and ********42 i have two number 42 is the aircel number 82 i dont know please help me

[@kunalbajaj3547]

@@Excelrush hii please help me ********42 this is Aircel number but this Aircel company is shut down what to do please help me

[@yojanak9331]

thnks alot

[@Excelrush]

Do subscribe to the channel for more such tips!

[@mommypowerrr]

hey, i have special conditions for column in google sheets which are: - should only be filled with numbers - no special characters allowed - no alphabets allowed - no space allowed - should be 16 digits in total i tried using =AND(LEN(A2)=16,INT(A2)=A2) which works in excel, but strangely not in google sheets. oh i also notice that the 16th digits always change into 0 in excel. can you please help?

[@Excelrush]

In data validation, use the formula =isnumber

[@mommypowerrr]

so it should be =AND(LEN(A2)=16,ISNUMBER)?

[@Excelrush]

=and (len(A2)=16,isnumber(A2))

[@mommypowerrr]

the output change into 1.23457E+15

[@Excelrush]

Make the format of the cells as NUMBER instead of GENERAL

[@mahamoudhassen2493]

i have searching for while and never seen as quick as simple you show, for that you have earned my subscribe and like hhh, anyway thank you very much