Automatically removing hyphens from ID# or SSN.
 

I am looking forward for a macro or any other way to convert my data:

12345-123456-1

when pasted into a column lets suppose C:C, to:

123451234561

i.e. hyphens removed automatically.
Can anyone please help?
Thanking in advance

Automatically removing hyphens from ID# or SSN.
 

Hi FARAZ QURESHI,

Select the data you wish to convert. Use Edit - Replace, and replace "-"
with nothing.

Ed Ferrero
www.edferrero.com

I am looking forward for a macro or any other way to convert my data:

12345-123456-1

when pasted into a column lets suppose C:C, to:

123451234561

i.e. hyphens removed automatically.
Can anyone please help?
Thanking in advance

Automatically removing hyphens from ID# or SSN.
 

Thanx Ed

But I have to consolidate several statements received in parts. Is there a
way 2 "automatically" replace the hyphens? A VBA Macro is welcomed.

"Ed Ferrero" wrote:

Hi FARAZ QURESHI,

Select the data you wish to convert. Use Edit - Replace, and replace "-"
with nothing.

Ed Ferrero
www.edferrero.com

I am looking forward for a macro or any other way to convert my data:

12345-123456-1

when pasted into a column lets suppose C:C, to:

123451234561

i.e. hyphens removed automatically.
Can anyone please help?
Thanking in advance

Automatically removing hyphens from ID# or SSN.
 

Hi Faraz:

Try this little gem. It replaces the active sheet only:

Sub hyphensconvertcolConly()
ActiveSheet.Columns("C:C").Replace _
What:="-", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Merss around with
ActiveSheet.Columns("C:C").

to change different sheets or columns:

as in activeworkbook.worksheets("Sheet1").Columns("D:G") .
......
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"FARAZ QURESHI" wrote:

Thanx Ed

But I have to consolidate several statements received in parts. Is there a
way 2 "automatically" replace the hyphens? A VBA Macro is welcomed.

"Ed Ferrero" wrote:

Hi FARAZ QURESHI,

Select the data you wish to convert. Use Edit - Replace, and replace "-"
with nothing.

Ed Ferrero
www.edferrero.com

I am looking forward for a macro or any other way to convert my data:

12345-123456-1

when pasted into a column lets suppose C:C, to:

123451234561

i.e. hyphens removed automatically.
Can anyone please help?
Thanking in advance

Automatically removing hyphens from ID# or SSN.
 

I tried writing an event macro to do this.

A couple of things that caused problems:

Pasting into a target cell doesn't fire the macro.

Excel insists on converting 123451234561 to scientific notation: 1.23E+11.
Setting the target format to:

Target.NumberFormat = "@"

does set the format to TEXT, however, it still displays as text 1.23E+11 not
text 123451234561. Widening the column didn't make a difference either.

I didn't save the code so I can't post it.

Biff

"Martin Fishlock" wrote in message
...
Hi Faraz:

Try this little gem. It replaces the active sheet only:

Sub hyphensconvertcolConly()
ActiveSheet.Columns("C:C").Replace _
What:="-", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Merss around with
ActiveSheet.Columns("C:C").

to change different sheets or columns:

as in activeworkbook.worksheets("Sheet1").Columns("D:G") .
.....
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"FARAZ QURESHI" wrote:

Thanx Ed

But I have to consolidate several statements received in parts. Is there
a
way 2 "automatically" replace the hyphens? A VBA Macro is welcomed.

"Ed Ferrero" wrote:

Hi FARAZ QURESHI,

Select the data you wish to convert. Use Edit - Replace, and replace
"-"
with nothing.

Ed Ferrero
www.edferrero.com

I am looking forward for a macro or any other way to convert my data:

12345-123456-1

when pasted into a column lets suppose C:C, to:

123451234561

i.e. hyphens removed automatically.
Can anyone please help?
Thanking in advance

Automatically removing hyphens from ID# or SSN.
 

Biff that is certainly a good point and the solution on the number
formatting, I believe, is to use NumberFormat = "0".

I would not recoomend an event macro in this situation.

My preferred solution would be some automated import facility whereby the
user clicks a button or selects something from the menu and then the files
are opened and imported and then cleaned either using Getopenfilename or a
file list on the sheet.

So Faraz you may need to add

ActiveSheet.Columns("C:C").NumberFormat = "0"

to the macro to format the cells.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"T. Valko" wrote:

I tried writing an event macro to do this.

A couple of things that caused problems:

Pasting into a target cell doesn't fire the macro.

Excel insists on converting 123451234561 to scientific notation: 1.23E+11.
Setting the target format to:

Target.NumberFormat = "@"

does set the format to TEXT, however, it still displays as text 1.23E+11 not
text 123451234561. Widening the column didn't make a difference either.

I didn't save the code so I can't post it.

Biff

"Martin Fishlock" wrote in message
...
Hi Faraz:

Try this little gem. It replaces the active sheet only:

Sub hyphensconvertcolConly()
ActiveSheet.Columns("C:C").Replace _
What:="-", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Merss around with
ActiveSheet.Columns("C:C").

to change different sheets or columns:

as in activeworkbook.worksheets("Sheet1").Columns("D:G") .
.....
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"FARAZ QURESHI" wrote:

Thanx Ed

But I have to consolidate several statements received in parts. Is there
a
way 2 "automatically" replace the hyphens? A VBA Macro is welcomed.

"Ed Ferrero" wrote:

Hi FARAZ QURESHI,

Select the data you wish to convert. Use Edit - Replace, and replace
"-"
with nothing.

Ed Ferrero
www.edferrero.com

I am looking forward for a macro or any other way to convert my data:

12345-123456-1

when pasted into a column lets suppose C:C, to:

123451234561

i.e. hyphens removed automatically.
Can anyone please help?
Thanking in advance

Automatically removing hyphens from ID# or SSN.
 

Pasting should fire the _change event. I'm guessing that in your testing, you
turned .enableevents off (maybe when one test version broke). And it was never
turned back on.

But this kind of thing worked ok for me--I wasn't sure about the formatting,
though.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myStr As String
Dim myCell As Range
Dim myRng As Range

Set myRng = Intersect(Me.Range("C:C"), Target)

If myRng Is Nothing Then Exit Sub

On Error Resume Next
For Each myCell In myRng.Cells 'just column C
With myCell
myStr = Replace(.Value, "-", "")
If IsNumeric(myStr) Then
.NumberFormat = String(12, "0")
Application.EnableEvents = False
.Value = myStr
Application.EnableEvents = True
End If
End With
Next myCell
On Error GoTo 0

End Sub


"T. Valko" wrote:

I tried writing an event macro to do this.

A couple of things that caused problems:

Pasting into a target cell doesn't fire the macro.

Excel insists on converting 123451234561 to scientific notation: 1.23E+11.
Setting the target format to:

Target.NumberFormat = "@"

does set the format to TEXT, however, it still displays as text 1.23E+11 not
text 123451234561. Widening the column didn't make a difference either.

I didn't save the code so I can't post it.

Biff

"Martin Fishlock" wrote in message
...
Hi Faraz:

Try this little gem. It replaces the active sheet only:

Sub hyphensconvertcolConly()
ActiveSheet.Columns("C:C").Replace _
What:="-", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Merss around with
ActiveSheet.Columns("C:C").

to change different sheets or columns:

as in activeworkbook.worksheets("Sheet1").Columns("D:G") .
.....
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"FARAZ QURESHI" wrote:

Thanx Ed

But I have to consolidate several statements received in parts. Is there
a
way 2 "automatically" replace the hyphens? A VBA Macro is welcomed.

"Ed Ferrero" wrote:

Hi FARAZ QURESHI,

Select the data you wish to convert. Use Edit - Replace, and replace
"-"
with nothing.

Ed Ferrero
www.edferrero.com

I am looking forward for a macro or any other way to convert my data:

12345-123456-1

when pasted into a column lets suppose C:C, to:

123451234561

i.e. hyphens removed automatically.
Can anyone please help?
Thanking in advance




--

Dave Peterson

Automatically removing hyphens from ID# or SSN.
 

you turned .enableevents off

Yes, that's what I did. I had it in the wrong place.

Thanks, Dave!

Biff

"Dave Peterson" wrote in message
...
Pasting should fire the _change event. I'm guessing that in your testing,
you
turned .enableevents off (maybe when one test version broke). And it was
never
turned back on.

But this kind of thing worked ok for me--I wasn't sure about the
formatting,
though.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myStr As String
Dim myCell As Range
Dim myRng As Range

Set myRng = Intersect(Me.Range("C:C"), Target)

If myRng Is Nothing Then Exit Sub

On Error Resume Next
For Each myCell In myRng.Cells 'just column C
With myCell
myStr = Replace(.Value, "-", "")
If IsNumeric(myStr) Then
.NumberFormat = String(12, "0")
Application.EnableEvents = False
.Value = myStr
Application.EnableEvents = True
End If
End With
Next myCell
On Error GoTo 0

End Sub


"T. Valko" wrote:

I tried writing an event macro to do this.

A couple of things that caused problems:

Pasting into a target cell doesn't fire the macro.

Excel insists on converting 123451234561 to scientific notation:
1.23E+11.
Setting the target format to:

Target.NumberFormat = "@"

does set the format to TEXT, however, it still displays as text 1.23E+11
not
text 123451234561. Widening the column didn't make a difference either.

I didn't save the code so I can't post it.

Biff

"Martin Fishlock" wrote in message
...
Hi Faraz:

Try this little gem. It replaces the active sheet only:

Sub hyphensconvertcolConly()
ActiveSheet.Columns("C:C").Replace _
What:="-", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Merss around with
ActiveSheet.Columns("C:C").

to change different sheets or columns:

as in activeworkbook.worksheets("Sheet1").Columns("D:G") .
.....
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"FARAZ QURESHI" wrote:

Thanx Ed

But I have to consolidate several statements received in parts. Is
there
a
way 2 "automatically" replace the hyphens? A VBA Macro is welcomed.

"Ed Ferrero" wrote:

Hi FARAZ QURESHI,

Select the data you wish to convert. Use Edit - Replace, and
replace
"-"
with nothing.

Ed Ferrero
www.edferrero.com

I am looking forward for a macro or any other way to convert my
data:

12345-123456-1

when pasted into a column lets suppose C:C, to:

123451234561

i.e. hyphens removed automatically.
Can anyone please help?
Thanking in advance




--

Dave Peterson

Automatically removing hyphens from ID# or SSN.
 

You're welcome, Biff.

"T. Valko" wrote:

you turned .enableevents off

Yes, that's what I did. I had it in the wrong place.

Thanks, Dave!

Biff

"Dave Peterson" wrote in message
...
Pasting should fire the _change event. I'm guessing that in your testing,
you
turned .enableevents off (maybe when one test version broke). And it was
never
turned back on.

But this kind of thing worked ok for me--I wasn't sure about the
formatting,
though.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myStr As String
Dim myCell As Range
Dim myRng As Range

Set myRng = Intersect(Me.Range("C:C"), Target)

If myRng Is Nothing Then Exit Sub

On Error Resume Next
For Each myCell In myRng.Cells 'just column C
With myCell
myStr = Replace(.Value, "-", "")
If IsNumeric(myStr) Then
.NumberFormat = String(12, "0")
Application.EnableEvents = False
.Value = myStr
Application.EnableEvents = True
End If
End With
Next myCell
On Error GoTo 0

End Sub


"T. Valko" wrote:

I tried writing an event macro to do this.

A couple of things that caused problems:

Pasting into a target cell doesn't fire the macro.

Excel insists on converting 123451234561 to scientific notation:
1.23E+11.
Setting the target format to:

Target.NumberFormat = "@"

does set the format to TEXT, however, it still displays as text 1.23E+11
not
text 123451234561. Widening the column didn't make a difference either.

I didn't save the code so I can't post it.

Biff

"Martin Fishlock" wrote in message
...
Hi Faraz:

Try this little gem. It replaces the active sheet only:

Sub hyphensconvertcolConly()
ActiveSheet.Columns("C:C").Replace _
What:="-", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Merss around with
ActiveSheet.Columns("C:C").

to change different sheets or columns:

as in activeworkbook.worksheets("Sheet1").Columns("D:G") .
.....
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"FARAZ QURESHI" wrote:

Thanx Ed

But I have to consolidate several statements received in parts. Is
there
a
way 2 "automatically" replace the hyphens? A VBA Macro is welcomed.

"Ed Ferrero" wrote:

Hi FARAZ QURESHI,

Select the data you wish to convert. Use Edit - Replace, and
replace
"-"
with nothing.

Ed Ferrero
www.edferrero.com

I am looking forward for a macro or any other way to convert my
data:

12345-123456-1

when pasted into a column lets suppose C:C, to:

123451234561

i.e. hyphens removed automatically.
Can anyone please help?
Thanking in advance




--

Dave Peterson

--

Dave Peterson