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 2And 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

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 uHere 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

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 SignA 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

message edited by mmcconaghy

I appreciate the answers, but this is all way over my head. :)

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)) = 59He means:

With your Password in cell A1 in cell B1 enter the formula: =CODE(MID($A$1,1,1)) which will return 59Now 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.

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.

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.

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

So try this:

Your Password

mustbe entered into cell A1In 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 NameHere 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 1E 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 - TildeAny questions please ask.

MIKE

message edited by mmcconaghy

Okay. I'm trying it. :) Thank you...

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

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 tableColumn 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 34Column 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 QuotesHere 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_-_TildeMIKE

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.

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

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#

You have an error in the Formula,

in the LOOKUP() section you have:$E$1:$E$95

it should be$E$1:$F$95Column E Row 1 : Column F Row 95

MIKE

message edited by mmcconaghy

There are TWO LOOKUP() sections make sure you change it in both sections...... MIKE

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 signNot 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 FunctionThis 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

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. :)

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 overSub ConvertChar()

then Press f5the 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 :)

Could you maybe email it to me? I could PM you my email address.

No Problem, pm me your email address

Sent you two files, the second one has a few additional symbols and outputs to Notepad and sheet2.....

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.!!!

~~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

message edited by mmcconaghy

How do I mark this as Solved?

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

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.

Ask Your Question

Weekly Poll

Do you think state sales tax should be charged in the United States on all Internet sales?

Discuss in The Lounge

Poll History