I need a formula that will remove forbidden characters and blank spaces.
Examples of characters I'd like to remove are .,’;<>?:”!@#$%^&*.
Thank you for any assistance you can provide!
For a regular worksheet formula, you can use nested SUBSTITUTE functions, with the nesting depth limited by the version of Excel you are using.
eg:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,".",""),",",""),"<",""),">","")
Extend as necessaary.
For a VBA solution:
To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.
To use this User Defined Function (UDF), enter a formula like
=RemForbidden(cell_ref)
in some cell.
=============================
Option Explicit
Function RemForbidden(s As String) As String
Dim re As Object
Dim sForbidden As String
Set re = CreateObject("vbscript.regexp")
'There are special rules for certain characters
' A hyphen must appear first or last
' A right bracket (]) must be preceded by a forward slash
' e.g: \]
sForbidden = ".,’;<>?:”!@#$%^&*"
With re
.Global = True
.Pattern = "[" & sForbidden & "]*"
End With
RemForbidden = re.Replace(s, "")
End Function
=====================================
