Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to Remove Forbidden Characters
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!
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 ===================================== |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying all whilst Range Forbidden active | Excel Programming | |||
Site for some reason, and forbidden | Excel Worksheet Functions | |||
Remove characters | Excel Programming | |||
Remove top bit characters | Excel Discussion (Misc queries) | |||
easy way to check for forbidden characters? | Excel Programming |