Thread: Using Excel to reformat a password with symbols

Hello! I'm trying to create a spreadsheet that will take what a user inputs (their username and password), reformats it, and then inserts that reformatted text into a predefined set of text.

For example, say a user's password is aBc12@/
I need it to be reformatted to: a, shift b, c, 1, 2, shift 2, SLASH
Then I will need to insert that into something like this:

ctrl ESCAPE, R, g, m, a, i, l, PERIOD, c, o, m, ENTER, n, a, m, e, TAB, a, shift b, c, 1, 2, shift 2, SLASH, ENTER

I have a sheet with one column listing A-Z and all of the symbols and the next column is the corresponding item (shift a-shift z and all of the keyboard equivalents for the symbols and names of symbols).
So far I have gotten it to work for the letters, upper and lowercase, and the numbers, but of course I am not able to get the symbols working. I know that Excel doesn't like the wildcards, but the VLOOKUP formula won't even work with the ` symbol (the formula below outputs ` instead of BACK_QUOTE).

Here is the formula I have so far. On the first sheet where the user would input the password, I have the following formula to separate each character into an individual cell:

=MID(Sheet1!$A$4,COLUMNS($B$1:B$1),1)

And on another sheet, I have the following formula that reformats the characters:

=IF(EXACT(Sheet1!C4,VLOOKUP(Sheet1!C4,Sheet2!A1:B58,1))=TRUE,VLOOKUP(Sheet1!C4,Sheet2!A1:B58,2),Shee t1!C4)

I haven't even started on combining the reformatted characters with commas or thinking about inserting it into the predefined set of text yet since I can't even get this part working.

Also, I cannot tell the users not to use symbols in their passwords since some applications that these passwords are used for actually require symbols.

Hello,
Some tips that may help...
1) If you leave out the fourth argument of the VLOOKUP formula (TRUE FALSE), it is assumed TRUE. This formula will only work if your list on Sheet2 is in ascending order. For exaple, when searching for the "@" symbol, if the formula encounters a character with a higher value than "@" it will stop looking and result in a N/A result, even if the "@" is somewhere (out of place) lower down the list. Generally, if there is only one possible match, it's best to use the FALSE argument. i.e. VLOOKUP(Sheet1!C4,Sheet2!A1:B58,1,false). FALSE requires the formula to look for a match in the entire list.
2) you can search for special characters by preceeding the character with a tilde ~ symbol, i.e. VLOOKUP ("~*",Sheet1!A1:B4,2,false) will look for the asterix character, instead of just-anything. I have a feeling that it's the ascending order that is causing the problem, and not the wildcard nature of some of these characters.
3) will you be able to stop your users from using special characters? It is possible to use countless characters that are not actually displayed on the keyboard, by using ascii codes i.e. ╔ Ê Ï ═ ♦ ♠ ♣ to name a few. Perhaps your task might be easier if you used the ascii value of the characters to begin with. In excel, you can determine the ascii value of any character by using =code("a") or =CODE(MID(Sheet1!$A$4, COLUMNS($B$1:B$1),1)) which will produce the number 97. Your list on sheet2 could include the ascii codes for the first-column lookup values, and your desired substitute text in the next column. (Note: =char(97) will do the reverse, and return the result "a".)
It's hard to provide more help without knowing the end-function of your sheet. But there's a start, anyways.
○f

Fantastic! All it took was just sorting the symbols A-Z and everything is fixed. Thanks so much!