There are times, in the life of a database administrator, when you just have to go back to good ol’ Excel. We use it for taking data apart, for finding duplicates and flagging them, for sorting up and down to find anomalies and errors. We use it to replace whole columns of one number with another.
Francis Scudellari from the Cloud for Good team started this discussion with this excellent post, Keeping it Clean: The Data Import Basics. Check that out to get started thinking about your data importing.
One of the most common uses, however is for putting cells TOGETHER. Usually this happens when I have a FirstName column and a LastName column and we need a Full Name column also. Sometimes we need to put one line of the address together with the second line. Excel calls this function “concatenation,” but I always think of it as “sticking things together.” Let’s see just how easy this is.
Here we have a list of names. Some are husbands and wives, but for the first example, all we want to do is get full names for everybody. Say I’m addressing envelopes for an invitation and I want to invite each individual. I’ve exported my contacts pages, and I’m looking at split names. I need to find a way to get them all into one cell.
Notice that I’ve already created the “destination” column. Your concatenation needs someplace to go!
Concatenate is at the top of my list because it is in the “Most Recently Used” category. You can also type “concatenate” into that description box and hit “Go.” Once you’ve highlighted CONCATENATE, click OK at the bottom of the box.
Enter all the cells (1) you wish to string together in the text boxes. Enter spaces by clicking in the cell, hit space, then click in the next box. Excel will add the space with quote marks around it. You can see your progress here: (2) To get to more, (3) simply scroll down. When you’re finished, click OK. (4)
Just start typing! Equals sign – so Excel knows you’re doing a formula. Then pick the function out of the list– CONCATENATE.
The easiest way to fill the column is simply to double-click on the little black box in the corner of the cell. That will fill everything below it with the same formula!
Almost…you still should get rid of all those formulas. If you move this column or add a new one or anything that messes up the formulas, it will be out of whack.
Be sure to paste VALUES!