Formatting a cell to REMOVE a hyphen?

jkalpus

New Member
Joined
Apr 17, 2004
Messages
46
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!!
 
It can't be formatted to do that, but you can use VBA to take it out. Is this acceptable?
 
Upvote 0
I used Column A, change as necessary:

Code:
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
 
Upvote 0
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
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top