From the course: Excel: Advanced Formulas and Functions
TEXTJOIN
- Let's look at the text join function. A lot of times we want to take values from multiple sales and string 'em together. In this case, we have the designation, last name, first name, middle initial. In row three, we have Dr. Maxine Moon. That's what we would like to have. In row six, we have Christine with a middle initial, middle name of Chris, Duncan. Let's use text join to put these together. Click here, put the data into a table. Go over here, format as table. Let's grab this color. Table does have headers. Okay, get rid of the filter buttons. Scroll down and make sure all of the data was captured. Yes it was. Okay, let's do it. Equals text join. Okay, double click text join. What is the delimiter? Meaning, what do we want each value separated by? In this case, just a space. Double quote, space, double quote, comma, ignore empty sales. Now, this is important for what we're doing. If somebody does not have a designation, yeah, we want it to be ignored. If somebody doesn't have a middle initial, we want to ignore it. So, I can either type in true or I can type in a one to say yes, ignore empty sales. I'm going to type a one, comma, now, it wants to text. The first text, I want the designation, comma, second text, the first name, comma, third, want the middle, comma, and then, the last name. Enter. Wideness out so we can see the beauty of what we have done. And here we go. Captain Shamus Llewheling. Good. Christine Chris Duncan. Scroll down. Yep. Dr. Giovanni Dawson. Now, let's look at one other thing. I'm going to scroll back up and go over here. Now, equals text join. The delimiter. Let's say I want all of the first names. So, I'm going to go double quote, space, and I want 'em separated by a space, asterisk, space, asterisk and space. Double quote, comma, do I want to ignore empty sales or not? This doesn't matter. So, I'm going to put a comma. And the default there is for true. Now, the text. I'm going to highlight the entire column and enter. And there we go. All of the first names are in one string. Okay? So, I didn't have to go row by row. It did take the column of names and put 'em all in one cell separated by space, asterisk, space. Let's look at one other thing. Cities, right? So, we've got this city data going from column B all the way through column H for city treasurer. And the data is incomplete. We don't have a population for everybody, or a city clerk for everybody. But, what I do want is I want city, city clerk, and county all in one cell. And now watch, I'm want to do this. I'm actually going to bring this down here. Okay? It always depends on what kind of situation you're in. You know, maybe this is all you want, then you're going to copy paste it into an email or something. Alright? But this is what we're going to do. We want equals text join, double click, delimiter. I want double quote, space, pipe symbol, space, double quote. Comma, ignore empty, false. I want to put a zero here, because if something is missing I want some idea that it is missing. Comma, text. The city is what I want first, so, click city, comma, text two, I want the city clerk's name, comma, I want the county. That there. Close the text join and enter. We've got the city of Anisette's data. Go over, drag this down. Okay, we're down to Greater Orange. Okay, drag down. Alright, and so this is where it's empty. We don't need this. But, notice what we have. For Catchings, there is a blank space in the middle because we do not have a city clerk's name. We can see for Laarbin, we do have a city clerk's name, but we don't have a county. So, that's a situation where you would not ignore blank. And I'm going to do one other thing. I'm going to go back to this formula. I'm going to go up top. I'm going to put and, and then double quote space, and then the word county, and double quote. Enter. So, now this says Logg County. Drag it down. Now we see Gliss County. And then this says just county, because we don't have the county information. And we can go back and say, oh yes, this is in Rock County. Enter, boom. It updated. And that's text join. Do not ignore blank or ignore blank.
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.
Download courses and learn on the go
Watch courses on your mobile device without an internet connection. Download courses using your iOS or Android LinkedIn Learning app.