Concatenating Text Columns in Excel

Adding “@colby.edu” to a List of Simple Usernames

What do you do if you need to send out a mailing and all you can have is an excel list of simple usernames: no @ sign, no “colby.edu”. 

Will you have to edit each one by hand? No!

These easy steps will save you a ton of work:

1. You will need 4 empty columns to the LEFT of the column of usernames.
 It should look something like this:
email-2
2. Insert the @ sign in the top cell of the second column – then copy and paste it into all the remaining cells of that column. email-3
 
email-4
3. Now enter “colby.edu” into the top cell of the third columns – then copy and paste that text into all the remaining cells in the column. email-5
 
email-6
4. Enter the function: =CONCATENATE(A2,B2,C2) into the top cell of the fourth column and press Enter.
Note: You will need to adjust the cell identifiers (A2, etc) to match the positioning of the data in your spreadsheet.
email-7
5. Copy and paste the function into the remaining cells. email-8
 6.  Now you need to copy the VALUES of the function in the last column into a new column that you will use for the mail merge.WHY?Since the text in the last column is actually the result of a FUNCTION, it:

1. May not insert correctly into your mail merge document. It might carry over as the “=CONCATENTATE” formula rather than the email address itself.

2. Will revert to an error if you delete either of the two work columns you created to create the email addresses

To Solve This:

Just COPY all the newly created email addresses and PASTE them into a new column using the Paste Values option under Past icon at the top left of the ribbon.

You can now delete all 3 extra columns you created since nothing is now pointing to any of them. And you will have usable email addresses.

 email-9