Telephone cell validation

dirty_cofi

New Member
Joined
May 5, 2002
Messages
13
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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
 
Upvote 0
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!
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top