Cell Formatting

Hello. I'm trying to reformat cell values imported from Access into Excel as Phone Numbers. I am able to change the cell formats but since the number imported from Access has an apostrophe in front of it (I assume this is why) the Phone Number format won't show unless I manually retype the number. Note that in Access this field has a (@@@)@@@-@@@@ format.
I'm using the code below to insert a new column then go down the rows & move the value to the new column. This eliminates the apostrophe. However the format in the cell still won't change unless I manually enter the number. Thanks in advance for any help offered.

Cells(1, 10).Select
Selection.EntireColumn.Insert
myStop = Cells(1, 1).CurrentRegion.Rows.Count
For i = 3 To myStop
If Cells(i, 9) = Empty Then
'No action is performed
Else
Cells(i, 10) = Cells(i, 9)
End If
Next i
Columns("I:I").Select
Selection.Delete shift:=xlToLeft
'Columns("I:I").Select
Selection.NumberFormat = "[<=9999999]###-####;(###) ###-####"
Cells(1, 10).Select

When the phone numbers are tranferred into excel are they like this
'5555555
or
'555-5555
or something else? Can you provide an example.

When the phone numbers are tranferred into excel are they like this
'5555555
or
'555-5555
or something else? Can you provide an example.


Hi Carl,
They are like this '5555555555

Okay, that's what I was hoping.
We need to change the cell's Formula to equal it's Value

'Assuming all phone numbers were in column A
For i = 1 To Worksheets(1).UsedRange.End(xlDown).Row
Cells(i, 1).Formula = Cells(i, 1).Value
Columns(1).NumberFormat = "###-####"
Next



obviously your numberformat is different.

Okay, that's what I was hoping.
We need to change the cell's Formula to equal it's Value

'Assuming all phone numbers were in column A
For i = 1 To Worksheets(1).UsedRange.End(xlDown).Row
Cells(i, 1).Formula = Cells(i, 1).Value
Columns(1).NumberFormat = "###-####"
Next



obviously your numberformat is different.


Carl,
Thank you for the help and so quickly too. That will work great.
Jimmy K