Cell Event Only Affects One Column

[Who I Am]

Hello:

One column is dedicated to phone number. When I copy-paste phone number
to it, I want all phone numbers in the same format like 999-999-9999. I
don't want to see 999.999.9999 or (999) 999-9999 or (999)-999-9999

I want this event gets triggered only after I paste the phone number
into a cell in the column and hit RETURN

Could you help?

 

[Ardus Petus]

Select whole column
Format>Cell>Number choose Customized
Enter: 000-000-0000

Et voilà!

HTH

 

[Who I Am]

Sorry it does not work.

 

[Ardus Petus]

Did you enter your phone numbers as decimals?
You must not include any - when typing your numbers.

You say it does not work.. what is the result? Is there an error msg?

HTH

 

[Who I Am]

The issue here is, I guess, that I do not type the phone number into a
cell in the column (if I type it, of course I will follow my own
format). I copy-paste them from different sources. They may look like
999.999.9999 or (999)-999-9999

I want a macro to change the format after I hit ENTER.

Per your suggestion, I format the column as 000-000-0000. But it does
not work.

Thank you for your prompt reply.

 

[Guest]

Try this Worksheet event: assumes data in column A

Private Sub Worksheet_Change(ByVal Target As Range)
dim strphone as string
On Error GoTo wsexit:
Application.EnableEvents = False
Set isect = Application.Intersect(Range("A:A"), Target) '<=== change A as
required
If isect Is Nothing Then
' MsgBox "Ranges do not intersect"
Else
strphone = Target.Value
strphone = Replace(strphone, ".", "")
strphone = Replace(strphone, "-", "")
strphone = Replace(strphone, "(", "")
strphone = Replace(strphone, ")", "")
Target.Value = Format(strphone, "000-000-0000")

End If
wsexit:
Application.EnableEvents = True
End Sub

 

[Guest]

Better ...
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo wsexit:
Application.EnableEvents = False
Set myRange = Intersect(Target, Range("A:A"))
If Not myRange Is Nothing Then
For Each mycell In myRange
With mycell
strphone = .Value
strphone = Replace(strphone, ".", "")
strphone = Replace(strphone, "-", "")
strphone = Replace(strphone, "(", "")
strphone = Replace(strphone, ")", "")
.Value = Format(strphone, "000-000-0000")
End With
Next mycell
End If
wsexit:
Application.EnableEvents = True
End Sub

 

[Who I Am]

It only works if I type 9999999999. If I copy 999.999.9999 to a cell,
it does not work.

So I think I must make all types of number strings, like 999.999.9999,
(999)-999-9999, to become no space (trim it), no punctuation. Then
apply your code.

 

[Guest]

I assumed data was input into a GENERAL/TEXT format field.

 

[Guest]

Copying 999.999.9999 from/to NUMBER cell works OK for me (XL2003)