?
Solved

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

Posted on 2016-07-29
7
Medium Priority
?
45 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
  • 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

840 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