UK telephone number & Data Validation

[ScottDavies]

I am creating a spreadsheet and in one column I want the user to be able to input an 11 digit telephone number (all numbers begin with a 0) without any spaces, ie: 01611111111

If they enter the wrong amount of digits (10, 12, whatever) I want them to get an error message.
If they enter a space I want them to receive an error message.

I'm not TERRIBLE at excel, but by no means an expert - does anyone have an idea on what I need to do? (You may be best telling me how to do it as if I'm a 5 year old!)

Thanks,
Scott

 

[Wookiee213]

You can use =LEN() to check the length and then combine that with =IF() to input a response or are you looking to use VBA?

 

[ScottDavies]

That went way over my head Wookiee

If I want numbers to be restricted to: 11 numbers long, beginning with a 0 (and have the 0 displayed), what do I need to do?

Cheers,
scott

 

[DavidStank]

If the User is not required to enter the 'leading zero', a check can be made that the User has entered the following 10 digits and the lead zero could be added at least one of two ways. As for checking for Users having entered 10 characters, an IF statement can be written using the LEN() function for other than 10 along with checking if the string of digits contains a space (e.g.: if the User entered "12345 4321" for 10 characters, the ISNUMBER() function returns FALSE).
I tried the Custom numbering format suggested above and a check formula of =IF(OR(LEN(A23)<>10,ISNUMBER(A23)=FALSE),"ERROR",A23) and it worked fine.
A formula could concatenate a zero in front of the User's entered 10 characters (this would make it TEXT and not a NUMBER) or, after checking that 10 digits have been entered, a Custom format of 11 digits can be applied to the cells. (Ctrl-1 to bring up Format menu, select 'Custom' under the Number tab and type eleven 0's into the window under 'Type:'
Custom formatted numbers of eleven characters do not count the lead zero as a character (I found this odd)

 

[DavidStank]

Sorry, a minor correction:
I tried the Custom numbering format suggested above and a check formula of =IF(OR(LEN(A23)<>10,ISNUMBER(A23)=FALSE),"ERROR",A23) and it worked fine.
Should read "I tried the Custom numbering format suggested BELOW . . ." where I then suggest the CTRL-1 and Number and Type: 00000000000.
Please let me know if this doesn't help you and I will give a more detailed explanation.

 

[Wookiee213]

That went way over my head Wookiee

If I want numbers to be restricted to: 11 numbers long, beginning with a 0 (and have the 0 displayed), what do I need to do?

Cheers,
scott

Chap below has given a decent answer for using a formula already so I'll leave it there mate - good luck

 

[BGY23]

First things first. Excel will not display Numbers with a leading Zero i.e. 0161 becomes 161. However you can make 161 appear as 0161.

So select cell A1. Click the Ribon Home>Format>Format cells. Select Number and then Custom. Now type 11 zeros into the type field. Click ok. Type a 1 into cell A1 and see what happens. 00000000001 right ? So mnow you can make 11 digits appear as you want.

The next trick is to check the number length but don't forget now you're dealing with 10 digit numbers not 11 (the 11th is the leading zero)

select A1 then Ribon Data>Data vlaidation on the setting tab select Allow = Whole number Data = Between Minimum = 1000000000 thats 1 and 9 zeros Maximum = 99999999999 thats 10 "9's". On the Error Alert tab type That number is not valid, Try again. click Ok

Now when your operators type 01612532534 (11 digits) excel converts it to 1612532534 10 digits but displays it as 01612532534. try typing too many or too few digits you'll get an error message.

When you've got a1 to work the copy and paste to as many other cells as you need.

 

[DavidStank]

Good reply, BGY23. I had time now to provide Scott a more detailed explanation of my solution but you've done a fine job with his problem. I have used the Custom format to fill in leading zeroes but I didn't know until today that Excel does not include those in the LEN() calculation . . . and I guess I can kind of see why.

 

[ScottDavies]

BGY, thank you very much, that was simple enough for me to follow and does exactly what I need. Much appreciated!

Thanks also to everyone else who took the time to respond, I appreciate it.

Scott

 

[BGY23]

Hi Dave and Scott,

Thanks for the feed back.

You're both welcome. Glad to have paid back some of the ehlp I've received on here.