Removal of non-alphanumeric characters but not foreign words 2

[snowmantle]

Hi I came across this code for removing non alphanumeric from a cell value in VBA.. but I have not tested it yet.

I was wondering if anyone knew of one that can be used for European languages as well. e.g. Taking Éé and Úú into consideration. Or is it better to do a like on symbols I dont want such as ,.!"£$%^&*()_-+={}[]@':;~#<>?/|\¬`

or maybe there is a regex for this very thing.

For i = 1 To Len(a$) 
  b$ = Mid(a$, i, 1) 
  If b$ Like "[A-Z,a-z,0-9]" Then 
      c$ = c$ & b$ 
  End If 
Next i

 

[stevexff]

A regex would undoubtedly be more efficient, but specifying which characters you want to keep is going to be the problem. I'm not aware of a predefined character class that will do what you want, so you might need to specify them.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[PHV]

Replace this:
If b$ Like "[A-Z,a-z,0-9]" Then
with this:
If Not (",.!""£$%^&*()_-+={}[]@':;~#<>?/|\¬`" Like "*" & b$ & "*") Then


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HarleyQuinn]

If you want a regex, you can use the Hex values of the ASCII characters you want to include. Something like (using ASCII characters 192-231 which in the regex are highlighted in red):

Public Function RemoveNonAN(strValue As String) As String
Dim re As Object
Dim mc As Object
Dim m As Object

Set re = CreateObject("VBScript.RegExp")

With re
    .Global = True
    .MultiLine = True
    .IgnoreCase = True
    .Pattern = "[[red]\xC0-\xE7[/red]A-Z0-9]"
    Set mc = .execute(strValue)
End With

For Each m In mc
    RemoveNonAN = RemoveNonAN & m.Value
Next m
End Function

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[HarleyQuinn]

Sorry, as Regex go, that one I posted was my testing one and is criminally inefficient

The below is better:

Public Function ReplaceNonAN(strValue As String) As String
Dim re As Object
Dim mc As Object
Dim m As Object

Set re = CreateObject("VBScript.RegExp")

With re
    .Global = True
    .MultiLine = True
    .IgnoreCase = True
    .Pattern = "[^\xC0-\xE7A-Z0-9]"
    ReplaceNonAN = .Replace(strValue, "")
End With

End Function

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[snowmantle]

Thanks for the replies, great as always. Both suggested solutions look good I will try them out tonight. Thanks

I take it VBScript.RegExp requires a reference?

 

[HarleyQuinn]

No, it's a late binding to the Regular Expression library. I've done it this way so you won't have to include the reference (the only downside is that if you want to modify it there is no intellisense).

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com