ExcelBanter - How to Remove Forbidden Characters

On Wed, 11 Jul 2012 14:33:25 +0000, rsexcel wrote:

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
=====================================