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

 

[Adityaat]

Welcome to the MrExcel board!

"\\|/|:|\*|\?|""|<|>|\|| "

So, you keep adding characters between "|" but precede any "special" characters" with a "\". To determine if your character is a "special" character, you need to look up information about regular expressions (here's just one), or do some trial & error to see what happens with & without the escape "\".

Post back with details of your characters-of-interest if you need more help.

Thanks for clearing up the "escape" property. I was reading so many other resources and couldn't get my head around it (I have very elementary programming experience). Here's what I did:
1. Created a test string (within quotes) - "rem&char.s : -test,™®©".
2. I understood that the "|" is an OR operator so I already tried removing "&" and "-" in this way but that didn't work. Here's what I tried
.Pattern = "\\||&|/|:|\*|""|\?|<|>\|| "
.Pattern = "\\||"&"|/|:|\*|""|\?|<|>\|| " - This broke the pattern into two groups so I guess & is acting as an AND operator and not a character.
.Pattern = "\\||-|/|:|\*|""|\?|<|>\|| "
.Pattern = "\\||"-"|/|:|\*|""|\?|<|>\|| "
3. Going by your point on the escape character. I tried (adding each character successively) but can't get to the result desired:
.Pattern = "\\||\.|\&|\-|,|\©|\®|\™|/|:|\*|""|\?|<|>\|| "

The extra characters I need removing are in red.
Thanks again for your help with this Peter!

Aditya

 

[Peter_SSs]

.. can't get to the result desired

It might help if you stated exactly what that desired result is, otherwise we are just guessing from patterns that apparently don't do what you want.

Also worth describing in words, what you are trying to do.
And perhaps a few more samples and their expected results.

 

[Adityaat]

I have a column with a item descriptions from a website and am trying to strip the text off of these special characters. For example, I have a product description:
[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]23-7/8"L x 8-"W x 7"H Red Polypropylene High Capacity Stacking Bin[/TD]
[/TR]
</tbody>[/TABLE]

There are other examples where the registered & trademark symbols are present and I would like to remove all of these at one go instead of using substitute function that depends on search pattern. Hope this makes sense.

 

[Adityaat]

I just had to close excel and restart it and it worked!

Thanks again for the help Peter.

 

[Peter_SSs]

Thanks again for the help Peter.

You are welcome.

I just had to close excel and restart it and it worked!

Glad you got it going. The reason that worked where the function hadn't previously, is the way the code is structured. If you use the function and then change the Pattern line and use the function again, the code skips that Pattern line because RegEx is 'Static' and remains existing (with the previous Pattern) from the first us. Closing Excel would have reset that & then picked up the new Pattern.

I originally put the Pattern line inside that If ... End If block to save time if a large data set is being used. However, it is probably safer (& certainly while testing different patterns) to keep the Pattern line outside that block.

As you will notice, I posted that quite a few years ago and now would more likely do this, so any changes to the pattern are picked up.

Function RemChrs(s As String) As String
    Static RegEx As Object
    
    If RegEx Is Nothing Then
        Set RegEx = CreateObject("VBScript.RegExp")
        RegEx.Global = True
    End If
    RegEx.Pattern = "\\|/|:|\*|""|\?|<|>\||\.|&|-|™|®|©| |:"
    RemChrs = RegEx.Replace(s, "")
End Function

IF you have a large number of these functions in your worksheet and are certain of your Pattern, then you could put the Pattern line back where it was in my earlier code.

 

[bkelciov]

Hi,
I have compose this solution:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng0 As Range ', cell As Range
Set rng0 = Intersect(Range("E:E,I:I"), Target)
On Error GoTo ws0_exit:
Application.EnableEvents = False
If Not rng0 Is Nothing Then
For Each cell In rng0
cell.Value = Replace(rng0, "?", "_", 1)
cell.Value = Replace(rng0, "", "_", 1)
cell.Value = Replace(rng0, "/", "_", 1)
cell.Value = Replace(rng0, "<", "_", 1)
cell.Value = Replace(rng0, ">", "_", 1)
cell.Value = Replace(rng0, """", "_", 1)
cell.Value = Replace(rng0, "*", "_", 1)
cell.Value = Replace(rng0, "|", "_", 1)
cell.Value = Replace(rng0, ":", "_", 1)
cell.Value = Replace(rng0, ";", "_", 1)
cell.Value = Replace(rng0, "[", "_", 1)
cell.Value = Replace(rng0, "]", "_", 1)
cell.Value = Replace(rng0, "+", "_", 1)
cell.Value = Replace(rng0, "{", "_", 1)
cell.Value = Replace(rng0, "}", "_", 1)
cell.Value = Replace(rng0, "~", "_", 1)
cell.Value = Replace(rng0, "`", "_", 1)
cell.Value = Replace(rng0, "!", "_", 1)
cell.Value = Replace(rng0, "@", "_", 1)
cell.Value = Replace(rng0, "#", "_", 1)
cell.Value = Replace(rng0, "$", "_", 1)
cell.Value = Replace(rng0, "%", "_", 1)
cell.Value = Replace(rng0, "^", "_", 1)
cell.Value = Replace(rng0, "&", "_", 1)
cell.Value = Replace(rng0, "=", "_", 1)

Next cell
End If
ws0_exit:
Application.EnableEvents = True
End Sub

You can add or remove any character from list by adding or removing the entire line.
This sub work in the worksheet where is inserted. It replace the character after you finish editing the cell ( after hit "Enter" or move with arrows ).

 

[bkelciov]

Sorry. I was in rush and posted incomplet code. I forgot to activate cell As Range.
This tested working code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng0 As Range, cell As Range
Set rng0 = Intersect(Range("E:E,I:K"), Target) 'Here you can select multiple columns. In this case the columns are E and I trough K
On Error GoTo ws0_exit:
Application.EnableEvents = False
If Not rng0 Is Nothing Then
For Each cell In rng0
cell.Value = Replace(rng0, "?", "_", 1)
cell.Value = Replace(rng0, "", "_", 1)
cell.Value = Replace(rng0, "/", "_", 1)
cell.Value = Replace(rng0, "<", "_", 1)
cell.Value = Replace(rng0, ">", "_", 1)
cell.Value = Replace(rng0, """", "_", 1)
cell.Value = Replace(rng0, "*", "_", 1)
cell.Value = Replace(rng0, "|", "_", 1)
cell.Value = Replace(rng0, ":", "_", 1)
cell.Value = Replace(rng0, ";", "_", 1)
cell.Value = Replace(rng0, "[", "_", 1)
cell.Value = Replace(rng0, "]", "_", 1)
cell.Value = Replace(rng0, "+", "_", 1)
cell.Value = Replace(rng0, "{", "_", 1)
cell.Value = Replace(rng0, "}", "_", 1)
cell.Value = Replace(rng0, "~", "_", 1)
cell.Value = Replace(rng0, "`", "_", 1)
cell.Value = Replace(rng0, "!", "_", 1)
cell.Value = Replace(rng0, "@", "_", 1)
cell.Value = Replace(rng0, "#", "_", 1)
cell.Value = Replace(rng0, "$", "_", 1)
cell.Value = Replace(rng0, "%", "_", 1)
cell.Value = Replace(rng0, "^", "_", 1)
cell.Value = Replace(rng0, "&", "_", 1)
cell.Value = Replace(rng0, "=", "_", 1)

Next cell
End If
ws0_exit:
Application.EnableEvents = True
End Sub

You can add or remove any character or any other string from list by adding or removing the entire line.
You can rename rng0 and ws0 with any name you want.
This sub work in the worksheet where is inserted. It replace the character after you finish editing the cell ( after hit "Enter" or move with arrows ) only from columns specified in the range rng0 and only in edited cells.

 

[Raveendran]

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?

Function RemChrs(s As String) As String
Static RegEx As Object

If RegEx Is Nothing Then
Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
.Global = True
.Pattern = "\\|/|:|\*|""|\?|<|>\||()- "
End With
End If
RemChrs = RegEx.Replace(s, "")
End Function


how to add ()- in the code need to make some changes while doing that

 

[Raveendran]

Thanks peter, i have tried this and its working.
But ()- these are not removed after adding in the code also

 

[brodoplovac]

Libre office support regex in it's search/replace box. Just check "regular expressions" box in search/replace box.
In "find" field enter [^0-9], in "replace" field leave empty.

I had a list of about 40.000 phone numbers. It took libre office about ten seconds to accomplish removal of all non numeral signs.