Conert data in two colums to phone number (000)000-0000 format

[ka3pmw]

I am trying to get the data in 2 columns to format to phone numbers. The input mask works but the output it either the number w/o any delimiters or in the format 000-000-0000 which is ok but there are some that are (000)000-0000. I have used the design view to make the changes but to no avail. The original data was imported from excel. Is there a VBA routine that I can use?

[ka3pmw]

The topic should be convert data in two columns to phone number (000)000-0000 format not Conert data in two colums to phone number (000)000-0000 format.

[ItsMe]

The first thing I would do is decide whether the data you store will include special characters like hyphens or parenthesis. Either way, I would store a phone number as text data type. My preference is to store only the numbers and add the special characters when viewed by the user and maybe when being exported.

IIRC, you can add an input mask at the table level while in design view of your table and, when you do this, you will be provided with an option to store the special characters, too. As I mentioned, I would opt to not store the special characters.

If you need to clean up a phone number before you store it in your data base, because you are importing from an outside source, you can use the Replace function to get rid of stuff.

Here is an example of getting rid of a hyphen.
MsgBox Replace("555-555-1234", "-", "")
https://msdn.microsoft.com/en-us/lib.../gg264409.aspx

[ka3pmw]

I did that and it does not work. I am using windows 7 access 2007. Some of the fields are ok, some go in wand wind up one long number and some don't have the ( and ) in them.
The input mask is ok but it is not formatting the data on the table correctly. That is why I wanted to use a VBS routine to force the cell to display it correctly.

[ItsMe]

I did that and it does not work...

I do not know what you are referring to, what you have tried, and what it is that is not working. You will have to provide more details in order for us to help. You mention Excel, but we don't know if you are trying to routinely import. You mention two columns but we don't know if this is two columns in your spreadsheet or in your table. You mention an input mask but don't mention anything about a user interface.

[ka3pmw]

It is windows 7, access 2007. The data was imported from an excel spread sheet.
I don't know what I just did but it works now. I just had to remove one of the dashes from each number and it formats fine.

This can be closed now