Telephone cell validation

[dirty_cofi]

hey,

this there any way i could validate a cell so that the only numbers can be inputted and that the first 5 digits are in a bracket and that after 3 digits it adds a space. Example: 01286674110 with validation = (01286) 674 110

Thanks

[lenze]

The formatting is easy: Choose Format>Cells and select the custom option. In the type field, enter:

(#####)### ###

This will automatically insert the brackets and a space when someone enters an 11 digit number

[ This Message was edited by: lenze on 2002-05-08 14:10 ]

[ This Message was edited by: lenze on 2002-05-08 14:11 ]

[Yogi Anand]

On 2002-05-08 13:53, dirty_cofi wrote:
hey,

this there any way i could validate a cell so that the only numbers can be inputted and that the first 5 digits are in a bracket and that after 3 digits it adds a space. Example: 01286674110 with validation = (01286) 674 110

Thanks

I will do it as a two step process ...
1. DATA|VALIDATION|SETTINGS|Allow|Whole_Number
between 1111111 and 99999999999
2. CUSTOM format the number as "("00000")" 000 ###

Hope This Helps!

[dirty_cofi]

Hey,

THANKS your a life saver!!!!

Later

[Mark W.]

BTW, "validation" is quite different from "formatting".

[Yogi Anand]

On 2002-05-08 14:44, Mark W. wrote:
BTW, "validation" is quite different from "formatting".

Hi Mark:
Are you referring to the technicality -- in that the OP had asked for a Validation Rule, and what I proposed was a combination of Validation and Custom formatting ...

or

You are referring to some problem that my proposed solution might cause.

Your valuable comments will be greatly appreciated.

Regards!

[dirty_cofi]

hey,

sorry don't understand the question but basically your answer was perfect and did the job exactly like i wanted!

Any ideas on how to format a credit card number so it has a space after 4 digits then space after 5 then after 4 then after 5 digits like :

1234 12345 1234 12345

When i do it excel kills the last 4 numbers and changes then to zeros!

thanks

[RichardS]

Excel has a limit of 15 with numbers. Not sure how you are going to get around it, but I think a similar question has been asked before on this site. Try searching on credit card.
Richard

[Yogi Anand]

On 2002-05-09 23:36, dirty_cofi wrote:
hey,

sorry don't understand the question but basically your answer was perfect and did the job exactly like i wanted!

Any ideas on how to format a credit card number so it has a space after 4 digits then space after 5 then after 4 then after 5 digits like :

1234 12345 1234 12345

When i do it excel kills the last 4 numbers and changes then to zeros!

thanks

As RichardS said, you can do it using a number format. However you can simulate the collection of numbers as a text string and then use DATA|VALIDATION. This will not be fool-proof, but he following formula in DATA|VALIDATION will give you the capability that you asked for:

=AND(LEN(A5)=20,MID(A5,5,1)=" ",MID(A5,11,1)=" ",MID(A5,16,1)=" ")

the above formula is for DATA|VALIDATION for cell A5, where it ensures that the 5th character, 11th character, and 16th characters are 'space'; and the length of the string is 20. It does not check whether each character being entered is a numeric character or not.

Hope This Helps!