Remove all special characters and spaces in one go

JakeCardigan

New Member
Joined
Mar 2, 2011
Messages
4
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 :biggrin:
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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?
 
Last edited:
Upvote 0
Hi there,

Try a UDF:

Code:
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)," ","")
 
Upvote 0
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
 
Upvote 0
Hi there,

Try a UDF:

Code:
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?
 
Upvote 0
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 :)
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top