Hi,
I need to run some VBA code to clean Column D on various workbooks.
Column D contains a load of names from websites/apps with a load of non-Alpha characters.
I have found the below two functions online which are perfect for what I need to do. However they need to be done in order, first removing the accents, then removing all but required characters.
Furthermore, I was hoping there was a way I could just run the code to clean column D, rather than currently where it creates two functions which I then need to use and copy and paste the results back into column D. I haven't used VBA for a long time but I remember when I did I just hit F5 and actions were taken onto the current workbook.
Any help will be greatly appreciated. Here are the following :
Function StripAccent(thestring As String)
Dim A As String * 1
Dim B As String * 1
Dim i As Integer
Const AccChars = "ŠŽšžŸÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿ"
Const RegChars = "SZszYAAAAAACEEEEIIIIDNOOOOOUUUUYaaaaaaceeeeiiiidnooooouuuuyy"
For i = 1 To Len(AccChars)
A = Mid(AccChars, i, 1)
B = Mid(RegChars, i, 1)
thestring = Replace(thestring, A, B)
Next
StripAccent = thestring
End Function
Sub stripper()
End Sub
Function CleanCode(Rng As Range)
Dim strTemp As String
Dim n As Long
For n = 1 To Len(Rng)
Select Case Asc(Mid(UCase(Rng), n, 1))
Case 32, 46, 48 To 57, 65 To 90
strTemp = strTemp & Mid(UCase(Rng), n, 1)
End Select
Next
CleanCode = strTemp
End Function
Sub cleanrr()
I need to run some VBA code to clean Column D on various workbooks.
Column D contains a load of names from websites/apps with a load of non-Alpha characters.
I have found the below two functions online which are perfect for what I need to do. However they need to be done in order, first removing the accents, then removing all but required characters.
Furthermore, I was hoping there was a way I could just run the code to clean column D, rather than currently where it creates two functions which I then need to use and copy and paste the results back into column D. I haven't used VBA for a long time but I remember when I did I just hit F5 and actions were taken onto the current workbook.
Any help will be greatly appreciated. Here are the following :
Function StripAccent(thestring As String)
Dim A As String * 1
Dim B As String * 1
Dim i As Integer
Const AccChars = "ŠŽšžŸÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿ"
Const RegChars = "SZszYAAAAAACEEEEIIIIDNOOOOOUUUUYaaaaaaceeeeiiiidnooooouuuuyy"
For i = 1 To Len(AccChars)
A = Mid(AccChars, i, 1)
B = Mid(RegChars, i, 1)
thestring = Replace(thestring, A, B)
Next
StripAccent = thestring
End Function
Sub stripper()
End Sub
Function CleanCode(Rng As Range)
Dim strTemp As String
Dim n As Long
For n = 1 To Len(Rng)
Select Case Asc(Mid(UCase(Rng), n, 1))
Case 32, 46, 48 To 57, 65 To 90
strTemp = strTemp & Mid(UCase(Rng), n, 1)
End Select
Next
CleanCode = strTemp
End Function
Sub cleanrr()