Solved

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

Posted on 2016-07-29
7
42 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

728 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