Create full email addresses from Lehigh Usernames in a Spreadsheet

If you have an Excel spreadsheet that contains a column of email addresses, Gmail lets you email that group of people simply by pasting the column of addresses into the "To:" field in a Composition window. You do not need to make an email list first if you don't need one.

However, if the data you want to paste is a list of only 4- or 6-digit Lehigh IDs, you must first append "@lehigh.edu" to all the IDs. All mass email messages must be sent to full email addresses. Failure to do so can result in the wrong person or people receiving your email!

This is because when Gmail searches 4-digit IDs against the Lehigh directory, it automatically selects a 6-digit ID if it finds one that starts with the same 4-digits. For example, you may intend to email user "aaa4." But if there is a user with the ID "aaa405" that person would receive the email message instead.

Follow these instructions to create full email addresses when the only Excel spreadsheet data you have are 4- or 6-digit User IDs.

1. Open your Excel file and review the User ID column. Ensure that there are no blank cells before continuing.

Insert two new columns to the right of the User ID column

Position the cursor in the field to the right of the first User ID and enter '@lehigh.edu

Note: A single apostrophe must precede this text.

2. Use Autofill to copy "@lehigh.edu" to the end of the list of User IDs. To do this, align the cursor at the bottom, right corner of cell B2 (as below), then drag the cursor to cell B7 and release.

3. This will automatically fill all of Column B with "@lehigh.edu."

4. Position the cursor in cell C2 and type '=concatenate(A2,B2)', then press Enter.

Notice the result in cell C2 is a combination of the User ID + @lehigh.edu, to make a complete address.

5. Use the Autofill technique again to copy the concatenate formula to the rest of the cells in Column C.

Align the cursor at the bottom, right corner of cell C2 (as below), then drag the cursor to cell C7 and release.

6. This will automatically fill all of Column C with the combined value of Column A (User ID) and Column B (@lehigh.edu).