Excel: Concatenating Fields

My next big task at work is adding our hundreds of branch locations to the Work Order & Preventative Maintenance (CMMS) system in Archibus.

Our consultant has a portal where we log in to the system, so, the first step is supplying them with a list of usernames and email addresses to import. The data I have available does not list this information together, so I needed a quick and easy way to produce it.

Concatenating a name to create an email address

While I could just copy the name column and do a find and replace to append the domain, I like concatenating, because it’s so quick and easy.

 

Just type an equal sign, the first cell name, an ampersand and then the second cell name.

Since I want to drag and copy this down the entire column, I still want to point the latter half of the equation to the top cell. I type a dollar sign in front of the cell number, so it will not increment to match the row number (if I were copying this formula to different columns, I would put a $ in front of both the column and the row, like “$F$1”).

 

 

If I were joining other data, such as the city and state fields, I would want to add additional formatting like a comma and a space. All I have to do is place some more &’s and surround the addition with a pair of quote marks.

 

The above is a shorter way of using the =CONCATENATE function.

 

MS Excel has an Autofill feature, so, start typing the word, and when it shows up at the prompt, hit Tab to insert the whole command into your cell.

 

 

Then proceed as you normally would when typing an expression in, using parentheses to surround the data and commas to separate the components you wish to include.