Formatting phone number in Excel from Access

[Euler271]

I'm trying to enter a formula into an Excel worksheet using VBA in Access. Specifically, I'm trying to format the phone numbers located in an adjacent column to where I'm trying to put the formula. I'm using the REPLACE function but I'm getting error messages. Here's the line:

xlWs.Range("AD2").Formula = "=REPLACE(REPLACE(AC2,4,0," - "),8,0," - ")"

You can see that I'm trying to enter hyphens in the fourth and eighth spots in the number. In other words,

xxxxxxxxxxx -> xxx-xxx-xxxx

Is there a better way to do this or how do I fix my function?

Thanks for the help.

 

[Euler271]

I found the answer to my own question: I just replaced the double quotes with two double quotes and it worked.

xlWs.Range("AD2").Formula = "=REPLACE(REPLACE(AC2,4,0,""-""),8,0,""-"")"

 

[welshgasman]

I would just use the Format property? and leave the data alone?

https://docs.microsoft.com/en-us/office/vba/api/excel.range.numberformat

 

[Euler271]

I would just use the Format property? and leave the data alone?

https://docs.microsoft.com/en-us/office/vba/api/excel.range.numberformat

Thanks for the suggestion.