Remove special characters Solved

Question

Hello Forum,

I need to extract data from a table that contains special characters and accents, and I can't find a foolproof method to "clean" the extraction.

In short, I only need to export non-accented letters and numbers: a-z, 0-9, A-Z.

Do you have any tips to help me out of this mess?

Thank you for your help, your time, and have a great end of the day everyone.

Farid

yg_be · Answer

Hello,
how do you perform the extraction?
Do you want to eliminate accented characters, or replace them with the corresponding non-accented characters?
One possible method is to write and use a VBA function that cleans up a string.

FaridP · Answer

Hello,

Thank you for your interest.

I found a VBA function to remove accents and replace them with the same unaccented character.

Secondly, I would like to remove all special characters without replacing them (@, %, #...).

If you have a VBA function to do that, I’ll take it.

Thanks again,

Farid

FaridP · Answer

I'm going to do better than that; I got a response that addresses all the issues on another forum, so I'm sharing it with you; it might be useful (again, a big THANK YOU to Micniv):

Public Function CharAllowed(ByVal s As String) As String
 ' 2020-02-20 MN Creation
 
 Dim s1 As String, i As Integer, s2 As String
 
 If Len(s) > 0 Then
 s = ChaineSansAccent(s)
 
 s1 = "0123456789abcçdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
 s2 = ""
 For i = 1 To Len(s)
 If InStr(1, s1, CStr(Mid(s, i, 1)), 0) = 0 Then
 Debug.Print "The character '" & Mid$(s, i, 1) & "' is not allowed"
 Else
 s2 = s2 + Mid(s, i, 1)
 End If
 Next i
 CharAllowed = s2
 End If
 
End Function

Private Function ChaineSansAccent(ByVal s As String) As String
 ' 2020-02-20 MN
 Dim s1 As String, s2 As String, i As Long
 
 s1 = "àâáãäåéèêëïîìíöôðòóüûùúñçÿÀÁÂÃÄÅÈÉÊËÌÍÎÏÒÓÔÕÖÙÚÛÜ"
 s2 = "aaaaaaeeeeiiiiooooouuuuncyAAAAAAEEEEIIIIOOOOOUUUU"
 For i = 1 To Len(s1)
 s = Replace(s, Mid$(s1, i, 1), Mid$(s2, i, 1))
 Next i
 ChaineSansAccent = s
End Function

You can use this to clean a column Champ1 in an UPDATE query:

UPDATE Table1 SET Champ1 = CharAllowed ([Champ1] )

FaridP · Answer

Honestly, I don't see what's "not very nice" about this approach. It could be helpful to more people.  I marked the topic as resolved.  Thanks again and have a good evening ????

FaridP · Answer

I don't share this point of view at all, but I can understand it.  Sorry to anyone I may have upset.  Have a great weekend,  Farid

FaridP · Answer

Better than that: I won't do any duplicates anymore ;-)

Remove special characters

6 réponses

Texture issues in enshrouded

Driver his

Peritel to hdmi conversion

How to access the bios on a compaq notebook

Dsi won't turn on anymore

Search for a song with the lyrics

Left mouse click, touchpad click inactive

Codes stronghold crusader

Stuck computer system repair

Disable write protection mp3 player