Automatically removing hyphens from ID# or SSN.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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").
......
 
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
 
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.
 
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
 
you turned .enableevents off

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

Thanks, Dave!

Biff
 
Back
Top