Data Validation Formula for Phone Numbers

[Newbienew]

I am having an issue with a lot of hands in the pot on the workbook I am working with. One area is the phone number area. Everyone puts it in different and I need it to show up in one format even if it is copied and pasted over with the 123 pasted option. When pasting with that option it takes out cell format. So I thought if i could put a formula that will only allow 10 digits to be entered as well as place it like (123) 456-7890 in a data validation it would work perfect.

Is this possible?

 

[navic]

So I thought if i could put a formula that will only allow 10 digits to be entered as well as place it like (123) 456-7890 in a data validation it would work perfect.

Perhaps
Select range of cells, go to format cell and in General tab -> custom -> put format->

(###) ###-####

In Data Validation set "Input Message"

Please enter phone number like 1234567890

Please see https://www.excelforum.com/excel-formulas-and-functions/943044-data-validation-phone-number.html

 

[Newbienew]

I attempted to do that but my issue when people use the copy and paste the Format Cell or Custom is not there any more when they paste the row of information. That is the biggest issue. I'll have 1234567890 (123) 456 7890 123-456-7890. I would like for the format not to change every time

 

[JoeMo]

I attempted to do that but my issue when people use the copy and paste the Format Cell or Custom is not there any more when they paste the row of information. That is the biggest issue. I'll have 1234567890 (123) 456 7890 123-456-7890. I would like for the format not to change every time

Data Validation is easily defeated by copy/paste rather than manual input. You could use VBA to prevent the user from using copy/paste, and to accept manual input only if it's in the format of your choice.

 

[Newbienew]

Id love to use a VBA but this workbook gets emailed and when it arrives on the other end, the coding seems to not be there.