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.
Click here to view my concatenate video tutorial.
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?
I use concatenate all the time! I have created many different functions using the CONCATENATE, FIND, LEFT, RIGHT and MATCH functions all together. It is quite useful! However, one thing to look out for is ensuring that if you are concatenating a integer or date/time, that it will be converted into text. That is the only drawback; but this can be remedied using INT() and DATEVALUE() as required.
LikeLike
Thanks for the tips, submeg! The more I learn about Excel, the more I realize I have to learn. There are so many incredible functions that can make life so much easier.
LikeLike
No worries! When I get back to work, I will email myself some of the tools I have created and will post them here so others can find the answers to problems that have taken me a few hours to solve at times
LikeLike
This guide has been extremely useful for Excel 2007. Really appreciate your sharing knowledge. Thank you.
LikeLike
Thanks Edwin! I really enjoy using Excel and I think people can really benefit from learning some basic time-saving functions. Glad I could help you!
LikeLike
Nice post. I was checking constantly this blog and I’m impressed!
Extremely helpful info specifically the last part 🙂 I care for such information a lot.
I was looking for this certain information for a long time.
Thank you and good luck.
LikeLike
Wonderful beat ! I would like to apprentice at the same time as you amend your website, how
could i subscribe for a weblog web site? The account aided
me a applicable deal. I had been a little bit familiar of this your broadcast provided brilliant clear idea
LikeLike
I absolutely love your blog and find most of your post’s to
be just what I’m looking for. Does one offer guest
writers to write content for yourself? I wouldn’t mind composing a post or elaborating on many of the subjects you write about here.
Again, awesome blog!
LikeLike
It’s going to be ending of mine day, except before end I am reading this great piece
of writing to improve my knowledge.
LikeLike
Have you ever considered creating an e-book or guest authoring on other sites?
I have a blog based upon on the same information you
discuss and would love to have you share some stories/information. I know my subscribers would value your work.
If you are even remotely interested, feel free to send me an e mail.
LikeLike
What’s Happening i’m new to this, I stumbled upon this
I’ve discovered It absolutely helpful and it has helped me out loads.
I am hoping to contribute & assist other users like its aided me.
Good job.
LikeLike