Excel user-defined formula to eliminate special characters

Question

I have got a function I use to replace names be for importing data into the payment-system, as this does not accept any Special characters.

Function UMLAUT(text As String)
'** Dimensionierung der Variablen
Dim umlaut1, umlaut2, umlaut3, umlaut4, _
umlaut5, umlaut6, umlaut7, umlaut8, umlaut9, _
umlaut10, umlaut11, umlaut12, umlaut13, umlaut14, _
umlaut15, umlaut16, umlaut17, umlaut18, umlaut19, _
umlaut20, umlaut21, umlaut22 As String

umlaut1 = Replace(text, "ü", "ue")
umlaut2 = Replace(umlaut1, "Ü", "Ue")
umlaut3 = Replace(umlaut2, "ä", "ae")
umlaut4 = Replace(umlaut3, "Ä", "Ae")
umlaut5 = Replace(umlaut4, "ö", "oe")
umlaut6 = Replace(umlaut5, "Ö", "Oe")
umlaut7 = Replace(umlaut6, "ß", "ss")
umlaut8 = Replace(umlaut7, "ó", "o")
umlaut9 = Replace(umlaut8, "&", "+")
umlaut10 = Replace(umlaut9, ";", ",")
umlaut11 = Replace(umlaut10, "é", "e")
umlaut12 = Replace(umlaut11, "á", "a")
umlaut13 = Replace(umlaut12, "à", "a")

UMLAUT = umlaut13

End Function

this does work fine, but is there a way, where I do not have to look for "new" Special characters every time I Need this. For example the he west data also contains an è, which is not exchanged and therefore the import in the banking-software does not work.

Thanks for any help! Max

Answer 1

How about something as simple as this?

Function replaceSpecialCharacters(givenString As String) As String

    Const SPECIAL_CHARS As String = "áéíóúýÁÉÍÓÚÝäëõöüÄËIÖÜ"
    Const REPLACE_CHARS As String = "aeiouyAEIOUYaeoouAEIOU"
    
    Dim i As Long
    
    For i = 1 To Len(SPECIAL_CHARS)
        givenString = replace(givenString, Mid(SPECIAL_CHARS, i, 1), Mid(REPLACE_CHARS, i, 1))
    Next i

    replaceSpecialCharacters = givenString

End Function

Answer 2

siddharth's code with additional comments plus

• the rpl_string for german-language;
• for characters eliminated (without replacement) you can put put in any sign you like

.

Function umlaut(text As String, Optional replaceEMPTYby As String = "")
    'great thx to Siddharth rout!
Dim umlaut1 As String, rplString As String
Dim i As Long, j As Long
Dim MyArray

    '~~> One time slogging
rplString = "EUR,,,f,,,,,,,S,,OE,,Z,,,,,,,,,,,(TM),s,,oe,,z,Y,,i,c,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,A,A,A,A,Ae,A,A,C,E,E,E,E,I,I,I,I,G,N,O,O,O,O,Oe,x,0,U,U,U,Ue,Y,b,ss,a,a,a,a,ae,a,ae,c,e,e,e,e,i,i,i,i,o,n,o,o,o,o,o,-,o,u,u,u,u,y,b,y" '<~~ and so on.
    '~~> The first one before the comma is empty since we do
    '~~> not have any replacement for character represented by 128.
    '~~> The next one is for 129 and then 130 and so on so forth.
    '~~> The characters for which you do not have the replacement,
    '~~> leave them empty
    'how to find out your own signs: in Excel in Cell A128 type formula =CHAR(ROW())
    'copy that down to 255. replace characters not wanted by the charcater wanted.
    'in B128 formula: =A128
    'in all cells from B129 down to 255 type/copy formula: =CONCATENATE(R[-1]C,"","",RC[-1])
    'paste the value from B255 in "rplstring" above!

If replaceEMPTYby <> "" Then
    rplString = Replace(rplString, ",,", "," & replaceEMPTYby & ",")
    rplString = Replace(rplString, ",,", "," & replaceEMPTYby & ",")
    rplString = Replace(rplString, ",,", "," & replaceEMPTYby & ",")
    If Mid(rplString, 1, 1) = "," Then rplString = replaceEMPTYby & rplString
    If Mid(rplString, Len(rplString), 1) = "," Then rplString = rplString & replaceEMPTYby
    Debug.Print rplString
End If

    MyArray = Split(rplString, ",")
    umlaut1 = text: j = 0

    For i = 128 To 255
        umlaut1 = Replace(umlaut1, Chr(i), MyArray(j))
        j = j + 1
    Next
    umlaut = umlaut1
End Function

Answer 3

What you need is a handy reference to THIS ASCII Table

Also

1. You do not need so many variables.
2. When you declare variables as Dim umlaut1, umlaut2, umlaut3 As String, then only the last variable is declared as string in VBA. The first two are declared as Variants

Now back to the ASCII table.

If you notice the special chars start from 128 and go up till 255 so simply use a loop to replace the unwanted characters.

NOTE: There is a one time slogging that you have to do. This will also ensure that you will not have to add more characters in the future. In the code below, simply add the text to be replaced in the same order as shown in the image above.

Code: (UNTESTED)

Function umlaut(text As String)
    Dim umlaut1 As String, rplString As String
    Dim i As Long, j as Long
    Dim MyArray

    '~~> One time slogging
    rplString = ",ue,e,,a,,,,,,,,......." '<~~ and so on.
    '~~> The first one before the comma is empty since we do
    '~~> not have any replacement for character represented by 128.
    '~~> The next one is for 129 and then 130 and so on so forth.
    '~~> The characters for which you do not have the replacement,
    '~~> leave them empty

    MyArray = Split(rplString, ",")

    umlaut1 = text: j = 0

    For i = 128 To 255
        umlaut1 = Replace(umlaut1, Chr(i), MyArray(j))
        j = j + 1
    Next

    umlaut = umlaut1
End Function

TIP: If you feel that you could have your solution by only considering up till say, ASCII 166, then simply amend the code accordingly :)

Answer 4

There are no easy tricks because you are using custom substitutions rather than deleting the characters. You can eliminate the extra String variables:

Function UMLAUT(text As String) As String
    UMLAUT = Replace(text, "ü", "ue")
    UMLAUT = Replace(UMLAUT, "Ü", "Ue")
    UMLAUT = Replace(UMLAUT, "ä", "ae")
    UMLAUT = Replace(UMLAUT, "Ä", "Ae")
    UMLAUT = Replace(UMLAUT, "ö", "oe")
    UMLAUT = Replace(UMLAUT, "Ö", "Oe")
    UMLAUT = Replace(UMLAUT, "ß", "ss")
    UMLAUT = Replace(UMLAUT, "ó", "o")
    UMLAUT = Replace(UMLAUT, "&", "+")
    UMLAUT = Replace(UMLAUT, ";", ",")
    UMLAUT = Replace(UMLAUT, "é", "e")
    UMLAUT = Replace(UMLAUT, "á", "a")
    UMLAUT = Replace(UMLAUT, "à", "a")
End Function