Cell Event Only Affects One Column
 

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?

Cell Event Only Affects One Column
 

Select whole column
FormatCellNumber choose Customized
Enter: 000-000-0000

Et voilà!

HTH
--
AP

"Who I Am" a écrit dans le message de
ups.com...
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?

Cell Event Only Affects One Column
 

Sorry it does not work.

Cell Event Only Affects One Column
 

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
--
AP

"Who I Am" a écrit dans le message de
oups.com...
Sorry it does not work.

Cell Event Only Affects One Column
 

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.

Cell Event Only Affects One Column
 

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


"Who I Am" wrote:

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?

Cell Event Only Affects One Column
 

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


"Toppers" wrote:

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


"Who I Am" wrote:

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?

Cell Event Only Affects One Column
 

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.

Cell Event Only Affects One Column
 

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

"Who I Am" wrote:

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.

Cell Event Only Affects One Column
 

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

"Toppers" wrote:

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

"Who I Am" wrote:

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.