How to Identify Encrypted User First/User Last Name in Excel

Sometimes, you may have a list of user names that are encrypted or obfuscated in some way, and you want to extract the first and last names from them. For example, you may have a report that shows the user names as rmorello, jamste, sebekkg, etc., and you want to get the first and last names as Ricardo Morello, James Stevens, Sebastian Kowalski, etc.

One way to do this is to use a formula in Excel that can identify the encrypted user first/user last name based on some rules or patterns. In this article, we will show you how to create such a formula, explain the basic theory behind it, and give you a detailed example with real numbers. We will also show you some other approaches that you can use to solve this problem.

The basic idea of the formula is to use some text functions in Excel to manipulate the encrypted user names and extract the first and last names from them. The text functions that we will use are:

• LEFT (text, num_chars): This function returns the leftmost num_chars characters from a text string.
• RIGHT (text, num_chars): This function returns the rightmost num_chars characters from a text string.
• MID (text, start_num, num_chars): This function returns a substring of num_chars characters from a text string, starting from the position start_num.
• LEN (text): This function returns the number of characters in a text string.
• FIND (find_text, within_text, [start_num]): This function returns the position of the first occurrence of find_text in within_text, starting from the position start_num. If find_text is not found, the function returns an error.
• SUBSTITUTE (text, old_text, new_text, [instance_num]): This function replaces old_text with new_text in text, either for all occurrences or for a specific instance_num. If instance_num is omitted, the function replaces all occurrences.

Using these functions, we can create a formula that can identify the encrypted user first/user last name based on some rules or patterns. For example, if the encrypted user name follows the pattern of first_name_initial + last_name + year_of_birth, such as rmorello1978, we can use the following formula to get the first and last name:

=UPPER (LEFT (A2,1))&". "&PROPER (MID (A2,2,LEN (A2)-6))

This formula works as follows:

• LEFT (A2,1): This returns the first character of the encrypted user name, which is the first name initial. For example, r.
• UPPER (LEFT (A2,1)): This converts the first name initial to uppercase. For example, R.
• MID (A2,2,LEN (A2)-6): This returns the substring of the encrypted user name, starting from the second character and ending before the last four characters, which is the last name. For example, morello.
• PROPER (MID (A2,2,LEN (A2)-6)): This converts the first letter of the last name to uppercase and the rest to lowercase. For example, Morello.
• UPPER (LEFT (A2,1))&". "&PROPER (MID (A2,2,LEN (A2)-6)): This concatenates the first name initial, a dot, a space, and the last name, separated by the & operator. For example, R. Morello.

Procedures

To use the formula to identify the encrypted user first/user last name in Excel, follow these steps:

1. Open a new Excel workbook and enter some encrypted user names in column A, starting from cell A2. For example:

2. In cell B2, enter the formula =UPPER (LEFT (A2,1))&". "&PROPER (MID (A2,2,LEN (A2)-6)) and press Enter. This will return the first and last name for the encrypted user name in cell A2. For example:

3. Drag the fill handle of cell B2 down to copy the formula to the rest of the column B. This will return the first and last names for all the encrypted user names in column A. For example:

4. You can format the column B as you like, such as changing the font, color, alignment, etc.

Example

To illustrate the formula with a detailed example, let’s use some real numbers for the encrypted user names and the year of birth. For example, suppose we have the following data in column A:

Using the same formula as before, we can get the first and last names in column B:

Other Approaches

The formula that we used in this article is based on the assumption that the encrypted user name follows the pattern of first_name_initial + last_name + year_of_birth. However, this may not always be the case. Depending on how the user names are encrypted or obfuscated, you may need to use different formulas or approaches to identify the first and last names. Here are some examples of other possible approaches:

• If the encrypted user name follows the pattern of last_name + first_name_initial + year_of_birth, such as smithj1980, you can use the following formula to get the first and last name:

=UPPER (MID (A2,LEN (A2)-5,1))&". "&PROPER (LEFT (A2,LEN (A2)-6))

• If the encrypted user name follows the pattern of first_name + last_name_initial + year_of_birth, such as johns1980, you can use the following formula to get the first and last name:

=PROPER (LEFT (A2,LEN (A2)-6))&" "&UPPER (MID (A2,LEN (A2)-5,1))

• If the encrypted user name follows the pattern of first_name + "_" + last_name, such as john_smith, you can use the following formula to get the first and last name:

=PROPER (SUBSTITUTE (A2,"_"," "))

• If the encrypted user name follows the pattern of first_name + "." + last_name, such as john.smith, you can use the following formula to get the first and last name:

=PROPER (SUBSTITUTE (A2,"."," "))

• If the encrypted user name has no clear pattern or delimiter, such as jonsmi, you may need to use a lookup table or a database that can match the encrypted user name with the actual first and last name. For example, you can use the VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]) function to look up the encrypted user name in a table that has the first and last names in another column. For example, if you have the following table in another sheet named “Lookup”:

You can use the following formula to get the first and last name:

=VLOOKUP (A2,Lookup!$A$2:$B$7,2,FALSE)