Avatar of Alex Campbell
Alex CampbellFlag for United States of America

asked on 

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

This is beginning of what I want to do.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"a","8"),"e","3"),"i","1"),"o","0")
Microsoft Excel

Avatar of undefined
Last Comment
Glenn Ray
Avatar of Glenn Ray
Glenn Ray
Flag of United States of America image

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
Avatar of Alex Campbell
Alex Campbell
Flag of United States of America image

ASKER

Great start, but what would need to be done to handle uppercase?
For example, i would change to Y.
Avatar of Alex Campbell
Alex Campbell
Flag of United States of America image

ASKER

Where would save the code to use the function?
ASKER CERTIFIED SOLUTION
Avatar of Glenn Ray
Glenn Ray
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Alex Campbell
Alex Campbell
Flag of United States of America image

ASKER

I will try the code, but I wasn't able to download the  xlsm file.
Avatar of Alex Campbell
Alex Campbell
Flag of United States of America image

ASKER

Works great. Thanks!
Avatar of Glenn Ray
Glenn Ray
Flag of United States of America image

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
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo