One more function beyond Concatenate and Join that is useful in so many different ways. Concat can be used to quickly create unique columns of data. But watch out for dates!
Academy ↓
Hey there stranger!
Concat Because Concatenate is too hard to Remember!
About this Tutorial
Featured Formulas
Video Transcript
0:00 Okay, so one of the most interesting Google Sheet formulas is concatenate. And what Concatenate does is well, the most common example is when we're combining first name and last name, and we have to get a full name and when we'll use concatenate.
0:18 But it's so funny because Conta concatenate is very hard to remember. In no way have I ever used this term outside of Google Sheets.
0:29 And so sometimes Concat is actually easier to type, right? We start typing concat, and I understand right, that concatenate, you never have to actually remember it.
0:40 You just have to sort of remember this cate. And then here we have it already selected, so we don't actually have to type it all out.
0:48 But concatenate is the answer when we're looking at pushing the first name and last name together. There's also join. You could do join with a, with a a space, but usually people will say, well, concatenate A will add a little space here and then this B column, and we'll do that all the way
1:10 down, right? And we'll get for full names. But what's interesting is that Conka is actually very useful here, not the full concatenate.
1:19 If we do this right, we come up with something that looks weird. Let's just concatenate the first name and last name, A two A, B, and B two.
1:30 And what's interesting about Conka is that there's only two values. It is like the shortened version of Concatenate. And Conka will only ever give you two values or allow you to combine two values.
1:43 And looking at this, right, we see this full name, Emmit, marsh, Corbin, Camacho, these, these full names without a space.
1:50 And this is very human unreadable, right? Like, well, sort of, we can see that this is a full name. We can see this.
1:59 What's interesting is that you couldn't use this to create sort of unique columns. So for instance, there are a certain number of people in the world that have a, the same first name, and there are a certain number of people in the world that have a same last name, right?
2:16 Some common like Smith and, and maybe even common names like Adam, right? But it's very rare that you're gonna find people with the exact same name.
2:26 Obviously the, the most common first name and the most common last names Will. And sometimes in some situations, what you can do when you're combining this first name and last name is use Cate.
2:38 You can actually use the, the shorter version, and it's much faster, right? To type instead of having to figure out concatenate.
2:46 And it's Concat. Again, one of the silliest reasons to use Concat is because you can't remember concatenate. It's too hard to remember, too hard to spell, but you never have to actually spell it.
2:59 You Never, never have to actually do it. We can also, again, if you are doing this combining first name and last name, what you're actually doing is combining first name, a space, and the last name.
3:09 So in fact, join sort of better a sort of a better solution or just a different solution because you can create the eliminator, the limiter, right here with a space, and then do the two values.
3:22 You can do a two a B two, and there you have the exact same answer for com, full name as you have.
3:32 And you don't have to remember concatenate, just the syntax is a little different. But I want to get across in this video that actually Conka has an interesting use case and can be used across a lot of data sets.
3:46 If you want to create a unique column where maybe you have something like a report status, and then you have a date, right?
3:55 Maybe the status and the date don't change very often but there's many statuses and many dates, and you can create sort of a unique column, which is like status date.
4:05 But I do wanna share with you one interesting thing that happens with dates. If you are combining dates into sort of a unique identifier if you do add 'em with something like Conka, you're going to actually get the original number, which is the number of days since January 1st, 1900 in
4:27 this case. 10 October 4th, 2022 is actually 44 8 38. That is literally the number of days since January 1st, 1900. And so, dates in Google Sheets are very weird because if you type out a date, like let's say 6, 10, 20, 22 this looks like and is considered June 10th, 2022.
4:54 But in actuality, the underlying data, what, what Google Sheets remembers and, and has is it's 44 7 22, or 44,722. So combining dates with Conka, you're gonna end up with these unique sort of numbers that don't really look like dates, right?
5:13 But we as Google Sheets users will now know this right from this video. And so just by creating unique columns is not necessarily what our end result is.
5:26 Maybe we want to create a unique column because we want to know you know, we want to have unique identifiers in filters when accessing that data set.
5:35 So this should give you at least one more option beyond just concatenate. Beyond join Conka sometimes is very useful if you're combining only two elements, two items, and it can be very good, again, for unique identifiers, a unique rows or columns inside your dataset.
0:18 But it's so funny because Conta concatenate is very hard to remember. In no way have I ever used this term outside of Google Sheets.
0:29 And so sometimes Concat is actually easier to type, right? We start typing concat, and I understand right, that concatenate, you never have to actually remember it.
0:40 You just have to sort of remember this cate. And then here we have it already selected, so we don't actually have to type it all out.
0:48 But concatenate is the answer when we're looking at pushing the first name and last name together. There's also join. You could do join with a, with a a space, but usually people will say, well, concatenate A will add a little space here and then this B column, and we'll do that all the way
1:10 down, right? And we'll get for full names. But what's interesting is that Conka is actually very useful here, not the full concatenate.
1:19 If we do this right, we come up with something that looks weird. Let's just concatenate the first name and last name, A two A, B, and B two.
1:30 And what's interesting about Conka is that there's only two values. It is like the shortened version of Concatenate. And Conka will only ever give you two values or allow you to combine two values.
1:43 And looking at this, right, we see this full name, Emmit, marsh, Corbin, Camacho, these, these full names without a space.
1:50 And this is very human unreadable, right? Like, well, sort of, we can see that this is a full name. We can see this.
1:59 What's interesting is that you couldn't use this to create sort of unique columns. So for instance, there are a certain number of people in the world that have a, the same first name, and there are a certain number of people in the world that have a same last name, right?
2:16 Some common like Smith and, and maybe even common names like Adam, right? But it's very rare that you're gonna find people with the exact same name.
2:26 Obviously the, the most common first name and the most common last names Will. And sometimes in some situations, what you can do when you're combining this first name and last name is use Cate.
2:38 You can actually use the, the shorter version, and it's much faster, right? To type instead of having to figure out concatenate.
2:46 And it's Concat. Again, one of the silliest reasons to use Concat is because you can't remember concatenate. It's too hard to remember, too hard to spell, but you never have to actually spell it.
2:59 You Never, never have to actually do it. We can also, again, if you are doing this combining first name and last name, what you're actually doing is combining first name, a space, and the last name.
3:09 So in fact, join sort of better a sort of a better solution or just a different solution because you can create the eliminator, the limiter, right here with a space, and then do the two values.
3:22 You can do a two a B two, and there you have the exact same answer for com, full name as you have.
3:32 And you don't have to remember concatenate, just the syntax is a little different. But I want to get across in this video that actually Conka has an interesting use case and can be used across a lot of data sets.
3:46 If you want to create a unique column where maybe you have something like a report status, and then you have a date, right?
3:55 Maybe the status and the date don't change very often but there's many statuses and many dates, and you can create sort of a unique column, which is like status date.
4:05 But I do wanna share with you one interesting thing that happens with dates. If you are combining dates into sort of a unique identifier if you do add 'em with something like Conka, you're going to actually get the original number, which is the number of days since January 1st, 1900 in
4:27 this case. 10 October 4th, 2022 is actually 44 8 38. That is literally the number of days since January 1st, 1900. And so, dates in Google Sheets are very weird because if you type out a date, like let's say 6, 10, 20, 22 this looks like and is considered June 10th, 2022.
4:54 But in actuality, the underlying data, what, what Google Sheets remembers and, and has is it's 44 7 22, or 44,722. So combining dates with Conka, you're gonna end up with these unique sort of numbers that don't really look like dates, right?
5:13 But we as Google Sheets users will now know this right from this video. And so just by creating unique columns is not necessarily what our end result is.
5:26 Maybe we want to create a unique column because we want to know you know, we want to have unique identifiers in filters when accessing that data set.
5:35 So this should give you at least one more option beyond just concatenate. Beyond join Conka sometimes is very useful if you're combining only two elements, two items, and it can be very good, again, for unique identifiers, a unique rows or columns inside your dataset.
Topics