?
Solved

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

Posted on 2016-07-29
7
Medium Priority
?
43 Views
Last Modified: 2016-08-04
This is beginning of what I want to do.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"a","8"),"e","3"),"i","1"),"o","0")
0
Comment
Question by:Alex Campbell
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41734985
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
0
 
LVL 1

Author Comment

by:Alex Campbell
ID: 41735019
Great start, but what would need to be done to handle uppercase?
For example, i would change to Y.
0
 
LVL 1

Author Comment

by:Alex Campbell
ID: 41735028
Where would save the code to use the function?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 27

Accepted Solution

by:
Glenn Ray earned 2000 total points
ID: 41735075
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
1
 
LVL 1

Author Comment

by:Alex Campbell
ID: 41735124
I will try the code, but I wasn't able to download the  xlsm file.
0
 
LVL 1

Author Comment

by:Alex Campbell
ID: 41735137
Works great. Thanks!
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41740122
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
1

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question