 Hey folks, Rod here, and today we're going to show another use of the concatenate function. Here we have a worksheet with some names that we just made up. In this format we have last name, first name, middle initial, and space for full names. Now let's fill in the fake last names, first names, and middle initials if they have one, and now we want to concatenate, meaning joining all these together into a list of full names that can be used for other purposes. Here's how you do that. Type in an equal sign and then type in the word concatenate with an open parentheses. Now click cell B2 for the first name then comma. Click cell C2 for the middle initial then comma, and then click cell A2, closed parentheses, for the last name and pressed enter. As you can see, there's a problem with the first name, middle initial, and last name being all linked together into one word. Let's delete this and fix the problem. To fix this all we have to do is change our formula with spacing. Return to our formula and add quotation mark space quotation mark comma after B2 comma. Now type quotation mark period then a space quotation mark comma after C2 comma. Now hit enter and what you get is correct spacing with a period after the middle initial. But since Laura and Luke don't have middle initials, their names are a little messed up with just a period in the middle of their names. So how do you fix this? The answer is you combine the concatenate function with count A and if functions. This may sound hard, but Excel makes it easy. You already know that the concatenation function joins several text streams into one cell. So let's check out the count A function. The count A function figures out if a cell is blank or not. In this situation that means count A will determine if an individual has a middle initial or not. If you type an equal sign followed by the word count A and then parentheses, then C2 closed parentheses, and then press enter you get a one if the name has a middle initial. Now flash fill the column and you get a one if there is a middle initial or a zero if no middle initial exists. Count A is ready. So now let's check out the if function. The if function is a logical test determining if a value is true or false. What you're testing for in this case is does the person have a middle initial or not? If they do, then you want to concatenate the whole name with the initial. If they don't, you want to concatenate just the first and last names. Now you're ready to concatenate full names correctly by combining all three functions. So type in an equal sign followed by the word if with an open parentheses. Then type the word count A with an open parentheses. Now click cell C2 for the middle initial with a closed parentheses. Next type in an equal sign and the number one. So far your formula says if the number and cell C2 equals one there's something else that you want Excel to do. So now let's tell Excel what we wanted to do by typing a comma after the number one. Then type the word concatenate with an open parentheses. Then click cell B2 for the first name comma. Then create a space by typing in quotation mark space quotation mark comma. Next click the cell C2 for the middle initial and then type in quotation mark period space quotation mark comma for the period after the middle initial. Now click cell A2 for the last name and type in a closed parentheses comma. At this point you have told Excel if cell C2 equals one then concatenate the first name which is B2. Then space the middle initial C2 with a period and the last name which is A2. But we know some names don't have middle initials so now you need to tell Excel what to do if the cell C2 equals zero. So type the word concatenate right after the last comma with an open parentheses. Then click cell B2 for the first name comma. Create a space by typing quotation mark space quotation mark comma. Then click cell A2 for the last name and type a closed parentheses. Add another closed parentheses to close the if statement. Press enter and you get the full name Frank J. Michaels concatenated with correct spacing and even the period correctly placed after the middle initial. Now flash fill down the column and you get the list of names correctly filled in. There you have it. People with a middle initial have it in the right place and people who don't have just their first and last names. All names have the right spacing and your data is ready to be used for any purpose you want. So that's how you combine concatenate count A and if functions to manage complex lists of names using Excel 2016. We hope you had fun with this and if you still have questions click our coaching link and we will work with you personally. Also we want to know if you liked the video so kindly send us some love in the comments. Be sure to subscribe and receive future videos automatically. Thanks so much for watching.