Remove all special characters and spaces in one go

[JakeCardigan]

Hi

I am looking for a formula to remove special characters and spaces from a cell

I have been using "substitute" but this requires me to know which character I want to remove and this isn't always known

I have tried looking at some macro solutions but became lost quite quickly

Any help would be much appreciated

Thanks

 

[Peter_SSs]

Do you have a list, or specification, of what constitutes a 'special character' for your circumstances?

Edit: Alternatively a list, or specification, of what you want to keep?

 

[JamesW]

Hi there,

Try a UDF:

Function removeSpecial(sInput As String) As String
    Dim sSpecialChars As String
    Dim i As Long
    sSpecialChars = "\/:*?""<>|"
    For i = 1 To Len(sSpecialChars)
        sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), " ")
    Next
    removeSpecial = sInput
End Function

Alt+F11 - Insert - Module - Paste it in.

You can add more special characters into the array if you wish.

Then in your cell, type: =SUBSTITUTE(removeSpecial(A1)," ","")

 

[jim may]

Try Clean(A1)

 

[hardeep.kanwar]

Try ASAP Utilities

http://www.asap-utilities.com/asap-utilities-upgrade.php

Its Very Fast and Good

 

[JakeCardigan]

Hey
Thanks for your replies.

JamesW - Itried the UDF, and it seems to work great - thanks

One further question if that is ok, now that I have stripped out the designated characters, I have been running a check down the column of data to find duplicates:
=COUNTIF($AE$3:$AE$502,AE3)>1

For some cells I am getting an error - could this happen if there are too many characters in a cell?

Cheers

 

[JamesW]

What is in cell AE3, and what error do you get?

 

[Peter_SSs]

Hi there,

Try a UDF:

Function removeSpecial(sInput As String) As String
    Dim sSpecialChars As String
    Dim i As Long
    sSpecialChars = "\/:*?""<>|"
    For i = 1 To Len(sSpecialChars)
        sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), " ")
    Next
    removeSpecial = sInput
End Function

Then in your cell, type: =SUBSTITUTE(removeSpecial(A1)," ","")

Just wondering why you did most of the substitutions in the UDF but the space using a worksheet function? Why not
- include the space in sSpecialChars = "\/:*?""<>| "
- use a null string in sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), "")
- and just =removeSpecial(A1) in the worksheet?

Probably could also use a Regular Expression UDF for this job too, though I'm not sure it would be any better/faster?

 

[JamesW]

Very good point Peter, it was simply a case of I was in a rush and already had the UDF in my VBA reference folder.

I thought about using Replace$(sInput, Mid$(sSpecialChars, i, 1), "") but then I thought if there was a space which is not produced by removing the special character then it would still be there: |abc 123\a| would result in "abc 123a". But you are right with the first point

I simply overlooked it and did what I normally do: Overlook the blindingly obvious/simple approach

 

[Peter_SSs]

Probably could also use a Regular Expression UDF for this job too, though I'm not sure it would be any better/faster?

So, having a go at this ..

<font face=Courier New><br><SPAN style="color:#00007F">Function</SPAN> RemChrs(s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Static</SPAN> RegEx <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> RegEx <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> RegEx = CreateObject("VBScript.RegExp")<br>        <SPAN style="color:#00007F">With</SPAN> RegEx<br>            .Global = <SPAN style="color:#00007F">True</SPAN><br>            .Pattern = "\\|/|:|\*|""|\?|<|>\|| "<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    RemChrs = RegEx.Replace(s, "")<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><br></FONT>

In worksheet use like

=RemChrs(A1)