| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Removing Non-Numeric Characters - Automatically - How? | Excel Discussion (Misc queries) | |||
| Copy Rows to another Sheet Automatically | Excel Worksheet Functions | |||
| Automatically update cells | Excel Discussion (Misc queries) | |||
| Excel file automatically opens - More Info | Excel Discussion (Misc queries) | |||
| Can I shut off Excel automatically removing the first 0 | Excel Discussion (Misc queries) | |||
Hybrid Mode
