We help IT Professionals succeed at work.

Would like custom function to make multiple substitution on words for use in passwords.

Alex Campbell
on
This is beginning of what I want to do.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"a","8"),"e","3"),"i","1"),"o","0")
Comment
Watch Question

Glenn RayExcel VBA Developer
Top Expert 2014

Commented:
This function will replace characters between two key sets - strBase and strSub.  You need to ensure that both sets have identical members and that their individual elements align.  I've used your four example letter substitutions in this example.  This is NOT case-sensitive; capital source letters will substitute just like lower-case letters (ex. "A" also changes to 8).

Function PW(strPWord As String)
    Dim strBase, strSub As String
    Dim c As Integer
    strBase = "abcdefghijklmnopqrstuvwxyz"
    strSub = "8bcd3fgh1jklmn0pqrstuvwxyz"
    For c = 1 To Len(strPWord)
        PW = PW & Mid(strSub, InStr(1, strBase, Mid(strPWord, c, 1), vbTextCompare), 1)
    Next c
End Function

Open in new window


Regards,
Glenn

Author

Commented:
Great start, but what would need to be done to handle uppercase?
For example, i would change to Y.

Author

Commented:
Where would save the code to use the function?
Excel VBA Developer
Top Expert 2014
Commented:
1) To handle uppercase characters differently (that is, if you want to change uppercase characters in your source string differently than their lowercase counterparts), one would modify the strBase and strSub strings and change the Mid function comparison argument to vbBinaryCompare
Function PW(strPWord As String)
    Dim strBase, strSub As String
    Dim c As Integer
    strBase = " abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
    strSub = " @bcd3fghYjklmn0pqr57uvwxyz4BCD3F6H1JKLMN0PQR57UVWXYZ"
    For c = 1 To Len(strPWord)
        PW = PW & Mid(strSub, InStr(1, strBase, Mid(strPWord, c, 1), vbBinaryCompare), 1)
        Next c
End Function

Open in new window


To access this user-defined function (UDF), you need to save it in a VBA module within your workbook. See the attached example workbook.

Note that there is no provision for any punctuation conversion; you'll have to add that to the two strings if desired.

-Glenn
EE_Q2890386.xlsm

Author

Commented:
I will try the code, but I wasn't able to download the  xlsm file.

Author

Commented:
Works great. Thanks!
Glenn RayExcel VBA Developer
Top Expert 2014

Commented:
Glad I could help.  If you found my solution satisfactory, could you close the question and select my comment above as the accepted solution for your question?

Thanks,
Glenn