Preparing Data for Import: Sticking Stuff Together
Posted by Trish Perkins on Feb 22, 2013 in Blog | 6 comments
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.
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.
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!
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.
Click in the first cell…You’ve added Mr. to your string.
Notice that I had to use up boxes for the spaces!
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.
Suppose we just want the First Name and the Last Name. Is there an easier way?
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.
Don’t forget the spaces
- 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.
- Notice the commas. They tell Excel that you want to add something. THEY ARE THE GLUE in our process!
- 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?
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!
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
Then paste it right back where it is. Voila– no more formulas, only text!
Be sure to paste VALUES!
6 Comments
Leave a Reply
Get Updates
Join the Community
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!
Sure thing Christine. Trish Perkins is actually the one who wrote this post.
Tal
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))
Thanks Glenn, great point!
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).
Further to Glenn’s comment, Excel 2013 has a new feature called Flash Fill that can help with automagically splitting data into separate columns.