Formula required

[bandit65]

Morning
I have approx 1000 numbers that contain the minus sign (-) within the number, is it possible to remove all of these with a single formula?

Current number appear like this[TABLE="width: 158"]
<tbody>[TR]
[TD]978-1-28211-73-0[/TD]
[/TR]
</tbody>[/TABLE]
Required number
978128211730

Thanks in advance!

 

[Youngdand]

Can you not use find and replace - with nothing?

 

[Youngdand]

else you can use the custom function i wrote.

Function CleanPunc(strIn As String) As String
    Dim objRegex
    Set objRegex = CreateObject("vbscript.regexp")
    With objRegex
     .Global = True
     .Pattern = "[^\w\s]+"
    CleanPunc = .Replace(strIn, vbNullString)
    End With
End Function

You will have to save this in a module, and save the file as an excel addin. then add the addin in to excel. but after that you can use it as per any normal excel function. eg, =cleanpunc(a1)