By Trish Perkins

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.

We start with a name issue.

media_1361470983080.png

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.

Click the formula button above your table. It looks like fx.

media_1361471216216.png

Notice that I’ve already created the “destination” column. Your concatenation needs someplace to go!

Insert function comes up and CONCATENATE is at the top of the list!

media_1361471272615.png

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.

Here you can construct your Concatenation.

media_1361471548893.png

Click in the first cell…You’ve added Mr. to your string.

media_1361471755968.png

Notice that I had to use up boxes for the spaces!

media_1361472003161.png

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)

You can see the formula in the formula line and the concatenated string in the cell itself.

media_1361473489039.png

Suppose we just want the First Name and the Last Name. Is there an easier way?

media_1361473618001.png

Just start typing! Equals sign – so Excel knows you’re doing a formula. Then pick the function out of the list– CONCATENATE.

Start by clicking in the first cell you want to copy into your new box.

media_1361473710369.png

Don’t forget the spaces

media_1361473775800.png
  1. Anything between two quotes ( ” ” ) tells Excel you want to add this text. For Excel, a space is a text. Here you can put in hyphens, whole words, or just your humble little space.
  2. Notice the commas. They tell Excel that you want to add something. THEY ARE THE GLUE in our process!
  3. Excel recognizes ampersands as well: & is the same as a comma for Excel.

Here’s my Full Name, all ready…but wait…what if I have hundreds of names?

media_1361474057442.png

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!

All done!

media_1361474162955.png

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.

First Copy the whole column

media_1361474310267.png

Then paste it right back where it is. Voila– no more formulas, only text!

media_1361474371289.png

Be sure to paste VALUES!

Trish Perkins

Trish Perkins

6 responses to “Preparing Data for Import: Sticking Stuff Together

  1. Thank You Tal! It took me forever to learn how to use this very useful function. Now when I need to refresh my memory, all the steps are in your handy blog post!

  2. The reverse is often needed to import into a CRM system. Try these in excel to split the firstname and lastname from a fullname column assuming the fullname is in cell A2:

    First: =LEFT(A2,FIND(” “,A2)-1)
    Last : =RIGHT(A2,LEN(A2) – FIND(” “,A2))

  3. It’s also worth noting that there’s a shorthand for the CONCATENATE function!

    =CONCATENATE(A2,” “,B2)
    is the same as
    =A2&” “&B2

    Very handy! 🙂

    And Text to Columns is another great de-construction tool (in this case, using [space] as a delimiter).

  4. Further to Glenn’s comment, Excel 2013 has a new feature called Flash Fill that can help with automagically splitting data into separate columns.

Comments are closed.