Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2016-07-29
7
Medium Priority
?
44 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
Independent Software Vendors: 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 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

Independent Software Vendors: 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!

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

618 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