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

 

[JamesW]

Looks good, but not much experience with Regular Expression UDFs.

Thanks for the heads up anyway Peter, I'll amend the UDF in my reference folder and put your new one in.

 

[Peter_SSs]

I'm not suggesting that mine is better than yours.
I'm just trying to practice regular expressions when I can.

 

[Martha_78]

Hi Peter,

I like your last post and have tried to incorporate it in a macro below, however to no avail. Would you be able to identify where I'm wrong, please?

Sub change()
Application.ScreenUpdating = False


Dim col As Range
Dim lr As Long
lr = ActiveSheet.Range("F5000").End(xlUp).Row
Set col = Range("F9:O" & lr)


For Each cell In Range(col)
cell = RemChrs(cell.Value)
Next cell


Application.ScreenUpdating = True
End Sub


Thanks in advance!

 

[Peter_SSs]

Hi Peter,

I like your last post and have tried to incorporate it in a macro below, however to no avail. Would you be able to identify where I'm wrong, please?

Sub change()
Application.ScreenUpdating = False


Dim col As Range
Dim lr As Long
lr = ActiveSheet.Range("F5000").End(xlUp).Row
Set col = Range("F9:O" & lr)


For Each cell In Range(col)
cell = RemChrs(cell.Value)
Next cell


Application.ScreenUpdating = True
End Sub


Thanks in advance!

One thing at least is the red line. You have already declared col as a Range object so you would be getting an error when trying to do Range(col).
Try changing that line to

For Each cell In col

If that doesn't fix it, you would need to provide more information as just saying something isn't working gives us nothing to go on.

 

[Martha_78]

One thing at least is the red line. You have already declared col as a Range object so you would be getting an error when trying to do Range(col).
Try changing that line to

For Each cell In col

If that doesn't fix it, you would need to provide more information as just saying something isn't working gives us nothing to go on.

Hi, thanks 'For Each cell In col' worked and the sub had no errors and run worked, however nothing changed in the data - all cell data remained as were originally, prior to running the sub.

You're right you need more info.: Well, what I'm trying to do is use this macro and your function to loop through a range of cells (named col) and remove any special characters (stated on your function under 'Pattern'), if they are present.

For example: if the data on cell F9 = "Savings A/C", by using this macro I can change it to "Savings A C" - i.e. The "/" has been replaced with a space " ". Similarly I'd like it to loop through all cells in range and do the same on each cell.

Thanks again

 

[Martha_78]

Hi Peter, I managed to achieve what I wanted as below code.

Notwithstanding, I'm sure you would have managed to give me same or similar result! Thanks a million!


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


Sub change()
Application.ScreenUpdating = False


Dim col As Range
Dim lr As Long
lr = ActiveSheet.Range("F5000").End(xlUp).Row
Set col = Range("F9:O" & lr)


For Each cell In col
cell.Value = RemChrs(cell.Value)
Next cell


Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

Glad you ended up with what you wanted.

 

[ivanosito]

Thanks! Fast and clean. Perfect for what I needed. I'm copy/pasting from a OCR'ed PDF fragments of Plato's books.

 

[Adityaat]

Hi Peter - thanks a lot for this. How do I put in more characters to be removed by this UDF

Thanks

So, having a go at this ..


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



In worksheet use like

=RemChrs(A1)

 

[Peter_SSs]

Hi Peter - thanks a lot for this. How do I put in more characters to be removed by this UDF

Thanks

Welcome to the MrExcel board!

It depends what those extra characters are.
That udf removes the following (note that it includes a space at the end)
"\/:*?""<>| "
The structure of the Pattern line is that each character (or group of characters) to be removed should be separated by a "|" character.
Initially then that pattern would look like this, showing the separator in red.
"\|/|:|*|?|""|<|>||| "

The problem is that may of the characters between those red "|" characters (including the "|" itself) are "special" characters when using regular expressions. To show that you want those characters taken literally and not use their "special" meaning in regular expressions, you escape (precede) them with a "\", shown in blue below

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

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.