Cleaning Data Archives

Using CONCAT and CONCATENATE in Microsoft Excel

While the CONCATENATE and CONCAT functions seem like they might be the same thing, there are small differences between the two functions. CONCAT is new for the 2016 version of Excel, so if you’re using 2013 or earlier this article may not apply.

CONCATENATE – This function combines two or more strings together to form one. Very useful when dealing with heavy blocks of text data.

Example:

The formula in this example combined the first name and the last name of each of the people in our list. Each unique piece of the concatenation was separated with a comma, and notice we had to insert an extra space character (” “) to provide the space in between ‘John’ and ‘Doe’.

CONCATENATE, option B:

Some people prefer to combine text strings using an Ampersand (“&”), which works the same way. Combine each of the cells you want to add together along with any additional punctuation or spacing:

concatenate

CONCAT – This function is new beginning in the 2016 version of Excel. It provides a very simple update to the same formula, and that is the ability to select an entire range of strings all at once in order to concatenate them.

Example: Combining Five Values together

Notice in this example I’m combining five letters together to form one large string. With CONCATENATE, I have to separate each string with a comma. With CONCAT, I can select the whole range at once to get the same result.

Depending on what type of data you’re combining together (or how much), this could mean a lot more work putting the formula together. To recreate the First Name / Last Name example, you’d have to add in spaces to the First or Last Name boxes, or create a separate cell that only had a space character (” “) so that the values were properly combined together.

CONCAT Formula

Top 5 Things to Know in Excel

I remember listing “Microsoft Excel” as a skill on my resume just out of college. The only functions I knew how to use were SUM() & Concatenate(). I also knew that to start an equation, I needed to put an equals sign at the beginning of the cell. “Why would I need to know anything else?” I thought.

Since then, I have slowly expanded my knowledge of Excel and present to you what I now believe are the top five things to know in Excel if you want to list it as a “skill” on your resume:

1. Pivot Tables

If there were only one feature of Excel you could learn in the 15 minutes before a job interview, it would be Pivot Tables. This allows you to summarize hundreds or thousands of lines of data in a few moments. Add, Subtract, Divide, Count, & Parse data with this feature and impress the higher ups.

Articles to Check Out:

2. INDEX-MATCH and/or VLOOKUP

VLOOKUP

VLOOKUP is a function you can use to lookup data from a table. Think of sales data. Imagine you had a table listing the dollar amount of sales by the date. VLOOKUP would allow you to lookup the sales $ on a specific day. There are certain limitations to this function, very specific ways the data must be structured, and quite frankly a little confusing given that there are usually easier ways to find the same information.

Check out: VLOOKUP Article

INDEX & MATCH

In my opinion these are the formulas you should learn before learning VLOOKUP. With these two functions together, you can not only lookup data from a data set, but you can match two sets of data together for QA purposes, ensure data is consistent across multiple reports, and much more.

Articles to Check Out:

3. Informational Functions & Boolean Logic

Boolean Logic can be distilled down into two words: TRUE & FALSE. Boolean Logic is a form of logic where you’re testing whether or not an argument is TRUE or FALSE. Based on the outcome of that test, you can then test for other criteria, write a certain Text string, or perform a function.

Articles to Check Out:

Boolean Logic
ISNUMBER()
ISBLANK()

Once you’ve learned to evaluate whether or not something is TRUE or FALSE, you can follow that up by wrapping the result in an IF() statement, i.e. IF this statement is TRUE, do THIS. IF not, do THAT.

4. Basic Text Formulas

There are a large group of TEXT() formulas that are built specifically to deal with, you guessed it, TEXT. These functions are very useful for pulling out only the part of the string you most need from a cell.

The most commonly used formulas (based on my experience) are found below:

5. How to use the IF formula

The IF function is vital in the Excel Ninja’s repertoire. It allows you to easily add conditions to your formulas. If the formula is TRUE, do this. IF it’s FALSE, do that.

NESTED IF statements are when a user stacks multiple IF statements on top of each other. Imagine you had a statement that could be TRUE or FALSE, and you built an IF statement around that. Then imagine that if that statement evaluates to TRUE, there would be another two situations that could occur. You would have to “nest” your IF statement within the first IF statement.

IF() Function

RANDBETWEEN – Excel Function

What it does: The RANDBETWEEN() Function generates a random number between two integers.

Plain English: Rather than generating a random number between 0 and 1, RANDBETWEEN() generates an integer between a given range of integers that you provide. For example, you may want to pick 6 random lottery numbers between 1 and 35. This formula could help generate those.

Arguments: RANDBETWEEN(lower number, higher number)