If you use Microsoft Excel on a regular basis, chances are you probably have some rather repetitive, mundane tasks mixed in with your usually fun and exciting job. Personally, I would much rather work on something (ANYTHING!) in Photoshop than analyze data in Excel, but I lean more toward creative than analytical anyway.
But alas, I am a business person and if I want to efficiently process any type of data, I need to use Excel. I have also used it for quick html coding, but I’ll cover that in a future blog.
Today, I think you should know about the ever-so-helpful concatenate function.
Concatenate is defined as the action of linking or combining. Excel offers this funtion to combine the information in multiple cells and you can add other information into the mix as well.
For example, say you have columns containing first name, last name and titles of 300 people and you decide you actually want that information together, such as John Smith, Baker.
Now you can spend the better part of the day hating yourself and re-typing each combination, hoping you don’t make any typos, or even for the speedy Excel user, you could copy and paste, but concatenate can make this action possible for all 300 in less than a minute.
Once you have your data in a spreadsheet and are ready to combine it, click in an empty cell in which you want the results, then click on the fx/insert function.
If “concatenate” is not in your recently used functions, just type “concat” in the search box and press “Go.” Select Concatenate from the list and press OK.
Now here’s where it can get a little intimidating, but a great feature Excel offers is the formula result, which shows how the final product will appear. Another helpful feature is you can simply click in the cells that contain the information you want concatenated into the order you want it concatenated. Don’t forget that if you want to add spaces or punctuation, you will also need to add that. You can either do so by adding it next to the cell number or in a separate text box, the choice is yours.
Once you have concatenated the string of data to your preference, it’s time to spread the love to all the other columns. This is super easy: click on the cell containing the formula, then position the cursor in the bottom left hand corner of that cell. The cursor should change from a large white plus sign to a thin black plus sign. Click on the corner of the cell, hold down the left mouse button and drag the formula to the bottom of your list.
Today I used the contatenate function to rename a series of product images going on amazon.com. I had an Excel sheet with the UPC code which is what the image name needed to be, but there were multiple part numbers so I needed to name them UPC#.pt01.jpg and so on. I could have added .pt01, .pt02 and .pt03 to each file name, but the more I handle each file, the more my chances of a typo increase. By creating the file names in Excel, I am eliminating human error as much as possible and yielding consistent results.
NOTE: When working with functions in Excel, if you will need to copy and paste the information elsewhere, be sure to copy the data and “Paste Values” so you’re copying and pasting the data, not the formulas. Otherwise, you could end up pasting an error value instead of data.
Do you use the concatenate function? How has it helped you save time?