Formatting a cell to REMOVE a hyphen?

[jkalpus]

Hi Group ... Is there a way to FORMAT a cell to REMOVE a hyphen that potentially may get entered into the cell? For instance, a user might enter UNV-6788. I would like to instruct Excel to simply ignore the "-" character and return UNV6788. Yes, I can use the SUBSTITUTE function to remove it to another cell, but I would like to simply format the call in the first place to ignore the hyphen. Any ideas? Thanks!!

 

[Scott Huish]

It can't be formatted to do that, but you can use VBA to take it out. Is this acceptable?

 

[Scott Huish]

I used Column A, change as necessary:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range
Set d = Intersect(Target, Range("A:A"))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
    For Each c In d
        c = Replace(c.Text, "-", "")
    Next
Application.EnableEvents = True
End Sub

 

[jkalpus]

That does the trick. Many thanks!
BTW, I would have preferred a formatting option, but thought the answer lay most propbably in a chunk of VBA. Isn't it odd that Excel allows for the addition of a character with a format, but NOT for the exclusion of a character?? Thanks again! - John