Convert password characters to words

November 17, 2015 at 06:07:11
Specs: Windows 7, Various
Not sure where I should post this, so please feel free to move if this is in the wrong Forum. Here's the situation, I have a need to convert a password to words. Say for example the password is ;#AmMX2 I need the display to show this:

Semi-Colon
Pound Sign
Uppercase A
Lowercase m
Uppercase M
Uppercase X
Number 2

And so on. The passwords are randomly generated, and could contain any uppercase/lowercase/numbers/symbols.

I have no idea where to start. I have Googled and tried out several websites, but nothing shows what I need to display. Maybe I need something with a Script? An Access database with an Input Query? Excel Formula? I am not a VBA person, so simple is best.

I thank you all for any help.

message edited by beachyhbt


See More: Convert password characters to words

Report •

#1
November 17, 2015 at 06:59:21
Hey beachy,

i have had a quick go at this with VBA, im not at the office today so dont have the resources to play much, but here is a quick and dirty way to achieve this, however this thus far does not cater for symbols, this can be added in, i'll be at the office tomorrow.

My example below, uses the password "2HELLOu" and yields the following result

Number 2
Uppercase H
Uppercase E
Uppercase L
Uppercase L
Uppercase O
Lowercase u

Here is the code, this can be placed in the IDE of Excel and run by placing the cursor on 'Sub ConvertChar' and hitting the f8 key to step in, or the f5 key to run.

The output is displayed in the debug (immediate) window.... if this is now visible go to 'View' and select 'immediate window' or press and hold ctrl and press G.

Sub ConvertChar()

    Dim iString As String

    iString = "2HELLOu"

    For i = 1 To Len(iString)

        b = Mid(iString, i, 1)

        If IsNumeric(b) Then
            Debug.Print "Number " & b
        End If

        If IsLetter(b) Then

            If b = LCase(Mid(iString, i, 1)) Then

                Debug.Print "Lowercase " & b
            Else
                Debug.Print "Uppercase " & b
            End If

        End If

    Next i

End Sub

Function IsLetter(strValue) As Boolean
    Dim intPos As Integer

    For intPos = 1 To Len(strValue)
        Select Case Asc(Mid(strValue, intPos, 1))
            Case 65 To 90, 97 To 122
                IsLetter = True
            Case Else
                IsLetter = False
    Exit For
        End Select
    Next
End Function


Report •

#2
November 17, 2015 at 07:04:50
You can use Excel with a ANSI Table and the MID() function, the CODE() Function and a VLOOKUP().

With your Password in cell A1 in cell B1 enter the formula:

=CODE(MID($A$1,1,1)) = 59

In cell B2 enter the formula: =CODE(MID($A$1,2,1)) = 35
In cell B3 enter the formula: =CODE(MID($A$1,3,1)) = 65
In cell B4 enter the formula: =CODE(MID($A$1,4,1)) = 109
In cell B5 enter the formula: =CODE(MID($A$1,5,1)) = 77
etc. etc.

This will return the ANSI code number of the Letters/Symbols of the string in cell A1.

You then use the Code Number to do a VLOOKUP() to an ANSI Table
and return the proper name of that code.

With your ANSI Table in Columns E and F like:

        E             F
1) Code Number  Character Name
2)    33        Exclamation point
3)    34        Quote marks
4)    35        Octothorp / Hashtag
5)    36        Dollar Sign

A ANSI Table from numbers 33 thru 126 should be all you need

We then wrap a VLOOKUP() around the CODE() function like:

=VLOOKUP(CODE(MID($A$1,1,1)),E1:F95,2,FALSE)
=VLOOKUP(CODE(MID($A$1,2,1)),E1:F95,2,FALSE)
=VLOOKUP(CODE(MID($A$1,3,1)),E1:F95,2,FALSE)

And you should get what your looking for.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#3
November 17, 2015 at 10:01:56
I appreciate the answers, but this is all way over my head. :)

Report •

Related Solutions

#4
November 17, 2015 at 10:44:23
It's only over your head for now. While the VBA solution may be a lot tougher to learn, Mike's formula based solution is rather elegant and shouldn't be that hard to understand - once it is broken down into its parts. One of the great benefits of asking tough questions is that you get a chance to learn some of the more advanced Excel techniques. However, they only look advanced. In reality, they are nothing more than the combination of some simple Excel functions.

I'm sure that Mike would be willing to explain it to you if you asked nicely. ;-)

I will point out that the instructions that Mike offered might be misleading.

When he says:

With your Password in cell A1 in cell B1 enter the formula:
=CODE(MID($A$1,1,1)) = 59

He means:

With your Password in cell A1 in cell B1 enter the formula:
=CODE(MID($A$1,1,1)) 
which will return 59

Now that you know that, his VLOOKUP method might make more sense. If not, just ask.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
November 17, 2015 at 11:11:40
Thanks. I"m trying to work through this in my head. I created a password in Cell A1 and put the formula in B1, but I'm getting a FALSE result.

Report •

#6
November 17, 2015 at 11:22:36
Reread my latest post. The actual formula to use is:

=CODE(MID($A$1,1,1))

which should return the ASCII code for the first character of the string in A1.

You may have used this:

=CODE(MID($A$1,1,1)) = 59

If so, you are asking Excel to tell you if one side of that "equation" is equal to the other.

In other words, if you were to use =A1=B1 in a cell, you are asking Excel if the value in A1 is equal to the value in B1. The result will either be TRUE (yes, they are equal) or FALSE (no, they are not)

Therefore, if you used =CODE(MID($A$1,1,1)) = 59 you are asking Excel this question:

Is the ASCII code value (CODE) for the first character in A1 (MID($A$1,1,1)) equal to 59. Unless that first character is a semi-column , the formula will return FALSE since only a semi-colon has an ASCII code of 59.

Just use =CODE(MID($A$1,1,1)) and you should see an integer value as the result. You can then use that value to "lookup" the actual symbol associated with that code by using a VLOOKUP table.

Does that help?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
November 17, 2015 at 11:32:41
beachyhbt,

DerbyDad is correct, I probably should not have framed my answer the way I did.

So try this:

Your Password must be entered into cell A1

In cell B1 enter this modified formula:

=IF(ISERROR(VLOOKUP(CODE(MID($A$1,ROW(),1)),$E$1:$F$95,2,FALSE)),"",VLOOKUP(CODE(MID($A$1,ROW(),1)),$E$1:$F$95,2,FALSE))

It is rather long, so Copy & Paste from here.

Drag down 20 rows, a 20 character password is probably as long as you'll ever find.

By way of an explanation, the reason the Password must go in cell A1,
is that I use the MID() function in concert with the ROW() function
to return each character position in the password.

Row 1 returns the First Character
Row 2 returns the Second Character
etc. etc.

The CODE() function returns the ANSI value of a single character.

The VLOOKUP() then uses the ANSI number, returned by the CODE()
function, to get the Character Name

Here is a Table of character codes with there Names,
It is 95 rows long, copy the list from here and paste into
columns E & F beginning at row 1

          E            F
  1) Code Number Character Name
  2)     33      Exclamation Point
  3)     34      Double Quotes
  4)     35      Octothorp / Hashtag
  5)     36      Dollar Sign
  6)     37      Percent Sign
  7)     38      Ampersand
  8)     39      Single Quote
  9)     40      Opening Parenthesis
 10)     41      Closing Parenthesis
 11)     42      Star / Asterisk
 12)     43      Plus Sign
 13)     44      Comma
 14)     45      Minus Sign / Dash
 15)     46      Period
 16)     47      Slash
 17)     48      Number 0
 18)     49      Number 1
 19)     50      Number 2
 20)     51      Number 3
 21)     52      Number 4
 22)     53      Number 5
 23)     54      Number 6
 24)     55      Number 7
 25)     56      Number 8
 26)     57      Number 9
 27)     58      Colon
 28)     59      SemiColon
 29)     60      Left Arrow
 30)     61      Equal Sign
 31)     62      Right Arrow
 32)     63      Question Mark
 33)     64      AT Symbol
 34)     65      Upper Case A
 35)     66      Upper Case B
 36)     67      Upper Case C
 37)     68      Upper Case D
 38)     69      Upper Case E
 39)     70      Upper Case F
 40)     71      Upper Case G
 41)     72      Upper Case H
 42)     73      Upper Case I
 43)     74      Upper Case J
 44)     75      Upper Case K
 45)     76      Upper Case L
 46)     77      Upper Case M
 47)     78      Upper Case N
 48)     79      Upper Case O
 49)     80      Upper Case P
 50)     81      Upper Case Q
 51)     82      Upper Case R
 52)     83      Upper Case S
 53)     84      Upper Case T
 54)     85      Upper Case U
 55)     86      Upper Case V
 56)     87      Upper Case W
 57)     88      Upper Case X
 58)     89      Upper Case Y
 59)     90      Upper Case Z
 60)     91      Opening Bracket
 61)     92      Backslash
 62)     93      Closing Bracket
 63)     94      Caret - Circumflex
 64)     95      Underscore
 65)     96      Grave Accent
 66)     97      Lower Case a
 67)     98      Lower Case b
 68)     99      Lower Case c
 69)     100     Lower Case d
 70)     101     Lower Case e
 71)     102     Lower Case F
 72)     103     Lower Case g
 73)     104     Lower Case h
 74)     105     Lower Case i
 75)     106     Lower Case j
 76)     107     Lower Case k
 77)     108     Lower Case l
 78)     109     Lower Case m
 79)     110     Lower Case n
 80)     111     Lower Case o
 81)     112     Lower Case p
 82)     113     Lower Case q
 83)     114     Lower Case r
 84)     115     Lower Case s
 85)     116     Lower Case t
 86)     117     Lower Case u
 87)     118     Lower Case v
 88)     119     Lower Case w
 89)     120     Lower Case x
 90)     121     Lower Case y
 91)     122     Lower Case z
 92)     123     Opening Brace
 93)     124     Vertical Bar
 94)     125     Closing Brace
 95)     126     Equivalency Sign -  Tilde

Any questions please ask.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#8
November 17, 2015 at 11:41:02
Okay. I'm trying it. :) Thank you...

Report •

#9
November 17, 2015 at 11:59:14
I'm obviously doing something wrong. Column E Contains the numbers with a right paren. 1) F contains the ANSI numbers, and F Contains the Character names. I copied the formula to all cells in B1 - B25, and I get nothing in Cell B1.

Was I not supposed to have the 1) etc in Column E?

Sorry to be so difficult.

message edited by beachyhbt


Report •

#10
November 17, 2015 at 12:21:12
In my post of the Table, the numbers at the far left with the Closing Parenthesis
character are the Row numbers on the Excel Sheet, do not use them in the table

Column E Cell 1 should have the header: Code Number
Column E cell 2 should begin your Code Numbers at 33
Column E cell 3 should be Code Number 34

Column F Cell 1 should have the header: Character Name
Column F cell 2 should begin your Character Names with: Exclamation Point
Column F Cell 2 should be Character Name: Double Quotes

Here is the Table without Row numbers and the Spaces between words have
been replaced with an Underscore.

Code Number  Character_Name
   33        Exclamation_Point
   34        Double_Quotes
   35        Octothorp_/_Hashtag
   36        Dollar_Sign
   37        Percent_Sign               
   38        Ampersand
   39        Single_Quote
   40        Opening_Parenthesis
   41        Closing_Parenthesis
   42        Star_/_Asterisk
   43        Plus_Sign
   44        Comma
   45        Minus_Sign_/_Dash
   46        Period
   47        Slash
   48        Number_0
   49        Number_1
   50        Number_2
   51        Number_3
   52        Number_4
   53        Number_5
   54        Number_6
   55        Number_7
   56        Number_8
   57        Number_9
   58        Colon
   59        SemiColon
   60        Left_Arrow
   61        Equal_Sign
   62        Right_Arrow
   63        Question_Mark
   64        AT_Symbol
   65        Upper_Case_A
   66        Upper_Case_B
   67        Upper_Case_C
   68        Upper_Case_D
   69        Upper_Case_E
   70        Upper_Case_F
   71        Upper_Case_G
   72        Upper_Case_H
   73        Upper_Case_I
   74        Upper_Case_J
   75        Upper_Case_K
   76        Upper_Case_L
   77        Upper_Case_M
   78        Upper_Case_N
   79        Upper_Case_O
   80        Upper_Case_P
   81        Upper_Case_Q
   82        Upper_Case_R
   83        Upper_Case_S
   84        Upper_Case_T
   85        Upper_Case_U
   86        Upper_Case_V
   87        Upper_Case_W
   88        Upper_Case_X
   89        Upper_Case_Y
   90        Upper_Case_Z
   91        Opening_Bracket
   92        Backslash
   93        Closing_Bracket
   94        Caret_-_Circumflex
   95        Underscore
   96        Grave_Accent
   97        Lower_Case_a
   98        Lower_Case_b
   99        Lower_Case_c
   100       Lower_Case_d
   101       Lower_Case_e
   102       Lower_Case_F
   103       Lower_Case_g
   104       Lower_Case_h
   105       Lower_Case_i
   106       Lower_Case_j
   107       Lower_Case_k
   108       Lower_Case_l
   109       Lower_Case_m
   110       Lower_Case_n
   111       Lower_Case_o
   112       Lower_Case_p
   113       Lower_Case_q
   114       Lower_Case_r
   115       Lower_Case_s
   116       Lower_Case_t
   117       Lower_Case_u
   118       Lower_Case_v
   119       Lower_Case_w
   120       Lower_Case_x
   121       Lower_Case_y
   122       Lower_Case_z
   123       Opening_Brace
   124       Vertical_Bar
   125       Closing_Brace
   126       Equivalency_Sign_-_Tilde

MIKE

http://www.skeptic.com/


Report •

#11
November 17, 2015 at 12:44:04
I think I'm a hopeless case. I corrected the columns so that E contains the Code Number and F the Character Name, but I still get no result in B1.

Report •

#12
November 17, 2015 at 12:48:59
What is the password in cell A1?

Copy the formula you are using and Paste it here on the forum.

Do you get any error messages or just a blank cell?

MIKE

http://www.skeptic.com/


Report •

#13
November 17, 2015 at 12:56:09
I'm using the formula

=IF(ISERROR(VLOOKUP(CODE(MID($A$1,ROW(),1)),$E$1:$E$95,2,FALSE)),"",VLOOKUP(CODE(MID($A$1,ROW(),1)),$E$1:$E$95,2,FALSE)) and getting a blank cell.

The password I used to test is password12#


Report •

#14
November 17, 2015 at 13:03:08
You have an error in the Formula,
in the LOOKUP() section you have: $E$1:$E$95
it should be $E$1:$F$95

Column E Row 1 : Column F Row 95


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#15
November 17, 2015 at 13:06:57
There are TWO LOOKUP() sections make sure you change it in both sections......

MIKE

http://www.skeptic.com/


Report •

#16
November 18, 2015 at 00:43:25
Just for completeness I have added in code for symbols. so for a password such as

!2HELLOu£

the code will return

Excalation mark
Number 2
Uppercase H
Uppercase E
Uppercase L
Uppercase L
Uppercase O
Lowercase u
Pound sign

Not all the symbols have been defined but they can be added within the 'SymbolName' function..

Dim SymbolNames(10) As String
Dim b

Sub ConvertChar()
    
    Dim iString As String

    iString = "!2HELLOu£"

    For i = 1 To Len(iString)

        b = Mid(iString, i, 1)

        If IsNumeric(b) Then
            Debug.Print "Number " & b
        End If
            
        If IsLetter(b) Then
            
            If b = LCase(Mid(iString, i, 1)) Then
                
                Debug.Print "Lowercase " & b
            Else
                Debug.Print "Uppercase " & b
            End If
        
        End If
        
        If Not IsLetter(b) And Not IsNumeric(b) Then
            
            Debug.Print SymbolName(Mid(iString, i, 1))
        
        End If
                
    Next i
    
End Sub

Function IsLetter(strValue) As Boolean
    Dim intPos As Integer
    
    For intPos = 1 To Len(strValue)
        Select Case Asc(Mid(strValue, intPos, 1))
            Case 65 To 90, 97 To 122
                IsLetter = True
            Case Else
                IsLetter = False
    Exit For
        End Select
    Next
End Function

Function SymbolName(iSymbol As String) As String

    Select Case iSymbol
        
        Case "!":
            SymbolName = "Excalation mark"
        Case "£":
            SymbolName = "Pound sign"
        Case "$":
            SymbolName = "Dolla sign"
        Case "%":
            SymbolName = "Percentage"
    End Select
        
End Function

This can easily be made into a front end in Excel or Access, where you enter a password in either an input box or a text field and have it return the result into another box or notepad for you to copy.... In Access a table can be created for each letter and symbol and then a query can be constructed which can pull all the data together... many options are available..

message edited by AlwaysWillingToLearn


Report •

#17
November 18, 2015 at 04:01:55
Mike, I changed the formula, but I'm still getting a blank cell in all the Cells in Column B.

AlwaysWilling, I see your VBA code, but I'm not sure what to do with it. LOL

Told you I was a novice. :)


Report •

#18
November 18, 2015 at 04:40:24
Hi beachy,

if you want to have a go here is the easiest way

Open Excel
Press and hold ALT the tap F11
When the window opens double click sheet 1
Paste the code in
Scroll to the top and place the cursor over

Sub ConvertChar()

then Press f5

the output should display in the immediate window below.....

if only there was a way to attach files on here :(

good luck in finding a solution :)


Report •

#19
November 18, 2015 at 04:46:46
Could you maybe email it to me? I could PM you my email address.

Report •

#20
November 18, 2015 at 04:51:00
No Problem, pm me your email address

Report •

#21
November 18, 2015 at 05:23:35
Sent you two files, the second one has a few additional symbols and outputs to Notepad and sheet2.....

Report •

#22
November 18, 2015 at 05:34:57
Thank you guys so very much. This is exactly what I need.

Mike, I tried to reply to your email, but for some reason, the email wouldn't send.

You guys are the best.!!!


Report •

#23
November 18, 2015 at 06:03:28
Don't know why a reply would not work, but glad you got the file OK.

Just checked my email and I did receive your reply.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#24
November 18, 2015 at 06:16:42
How do I mark this as Solved?

Report •

#25
November 18, 2015 at 06:23:57
To mark the thread as solved you will need to select one of the answers as the 'Best Answer' this will then automatically mark it as solved. Not sure how long you need to wait for the 'Best Answer' option to appear next to all the responses, but its a big green option that says something along to lines of 'Mark as best answer'

Glad you got a solution


Report •

#26
November 18, 2015 at 07:52:25
I can't select two best answers, so I'm not going to select just one. Thanks again. You all have no idea how much I appreciate your patience with me on this problem.

Report •

Ask Question