Viewers: in countries Watching now:
In this series on productivity, author Jess Stratton takes you through the latest tools that will help you run your business and life more efficiently. Each installment covers a particular feature or technique in a different online tool, such as Google Apps, Skype, YouTube, Mint.com, Etsy, and more. Learn about topics ranging from recording and publishing video chats to managing your finances online.
Note: Monday Productivity Pointers is currently on a break, but stay tuned for new tutorials!
In the last video, I showed you how to import a contact list by making sure your header fields matched up. That's a great trick, but it relied heavily on one major assumption: that both the exporting from and the importing to program both separate the contact's first and last name. This is also the biggest cause of data migration headaches when dealing with contact data. You export all your contact data with two separate columns for first name and last name only to find out that the importing to field has one field called Name.
In that field is both the first and the last name together. Now what? Today I'm going to show you another great trick to combine those columns using Excel formulas. This is just a really useful formula to know how to do anyway, and you'll probably find yourself finding other uses for it also. It uses a practice called string concatenation, that is, putting together two separate pieces of text into one big block of text. So let's get started. Here's the Excel file. And here's my first name column and my last name column.
So the first thing that we need to do, if we're going to concatenate these and make one extra column, is insert a new column so that we have something to work with. So I'm going to select this column right here, then I'm going to come over to my Insert menu and choose Insert Columns. This gives me a nice blank column to work with. Now that I've inserted the column, the first thing I'm going to do is give it my new header label. In this case, Name. So let's take this row to start, cell A2, which is the person's first name.
Now, because we're going to type in a formula, we tell Excel to expect a formula by inserting the equal sign in front of our text. So now I'm going to tell Excel that I want to use whatever data is in cell A2, which in this case is the word Josephine, plus a space, because that's what comes in between a first name and a last name. And then everything that's in the contents of cell B2, which is the last name. So let's put together A2.
Excel highlights the cell in question. And to concatenate a string, we signify that by using the ampersand sign. So, so far we've got Josephine, and now we want to put a space in there. And I signify that by putting in open quotes because this is a literal string, it's a space, so it's just plain text. I'll hit the spacebar on my keyboard. I'll put a closing quotes in because that's the end of our literal text.
And now I need to finish it. We need to get her last name in there. So, I'm going to concatenate that with another ampersand sign because there's more. And this time I'm going to type B2. So, to put it all together, the equal sign tells us that Excel is expecting a formula here. So take the contents of whatever is in cell A2, and add a space and whatever is in the contents of cell B2. If I hit the return key on my keyboard, it enters in that text.
Now, here's the really neat thing. I don't have to do that for every single row that I want to import. I can highlight that cell, hit Cmd+C on my keyboard because I want to copy that formula. I'm going to scroll down for as many lines as I want to. I could do everything, but in this case just to show you, I'm just going to do a few. And I'll hit Cmd+V to paste the data. And Excel is smart enough to know, or should I say, smart enough to assume, that I want to update that information with every single row.
So, as I click my mouse through these rows, you'll notice at the top that it's updating the row itself So look how easy that was. That took about thirty seconds, and we successfully got our new columns with the first name and the last names all together. I'll just show you one more neat thing that you can do now that you know how to concatenate a string. I'm going to go ahead and put another column in. I'm going to call this one User Name. Let's just say that when we import data we need to give these people a user name, and we want to quickly get the person's first initial of their first name followed by their last name.
You can expand on this formula. I'll put my equal sign in to let Excel know that a formula is coming. And now, instead of putting in the entire first name, I only want the first letter of the first name. So I'm going to use another formula, and that's called the Left Formula; which tells Excel that I only want a certain amount of characters from the left-hand side of whatever contents is in this cell. So I'm going to type left. I'll put an open parentheses in there. I want the left of cell A2.
One character. That's going to get me the J in Josephine. I'll close the parentheses. Now I need more, so I'm going to put in that ampersand character. And now I want whatever's in cell B2. So again, equal sign to tell Excel that there's a formula coming. Whatever is the first character in cell A2 and cell B2. I'll hit the Return key. And there's my username. And again, Cmd+C to copy it. I can click and drag to select all those cells.
And Cmd+V to paste it. So you can easily see what a useful skill string concatenation is. So take this, go import your data, and now you can always specify whether or not you need to import using the first name, last name or just one field called Name, that contains a combination of both of them.
There are currently no FAQs about Monday Productivity Pointers.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.