Hey there stranger!

Sign up to get access.

Take a list and make it Vertical, Using Split Join or RegexReplace

About this Tutorial

Create vertical lists in Google Sheets.

Video Transcript

0:00 There's a question from a better Sheetz member. Really interesting question. Um, say I have a list of something in here, C4, and I have these pipes that are delineating each of the words or phrases or anything.
0:18 And I want to produce a vertical list. Um, this is what I call a split joint or joint split. A split joint always sounds better.
0:28 And as you can see here, um, I will go through this, um, short formula right now. So here's what we have to do.
0:36 First. We have to split or take apart these things, uh, this. So we do split, um, C six is the delineator, the eliminator we want to, um, sorry.
0:51 C6 is the text. We want to split across this pipe. So this is like this in quotes. We put the pipe and then we want to split by each.
1:01 Yes. True. Um, we, and the other things are optional. And now you see, we have this, um, each of the parts are in a separate cell.
1:13 We don't want that. We want them all in one cell. So we go at the beginning and we add a join.
1:19 Uh, we join, this is the strangest part of this. All we're going to have this quote of what is the dilemma there.
1:27 I'll come back to this. Our value is going to stay the split. So we don't have to type anything else, except I'm going to go back to this join, how do we join them?
1:37 And they, they appear in a new line. And, and the idea is it's new line. Um, we have to add a new line here, how we do this on a Mac is I hold the command key and hit enter.
1:50 Uh, and that creates a new line within these quotes. So now when I hit enter again to do it, we now have this join on each of these new lines.
2:01 We can also, there there's a potential other way with replace and I'll show you that in one second, right? So we can also use replace our actually rejects replace here.
2:12 So we have the same exact thing, the same exact result, but we're using rejects replace. And I'll go through that with you in one hot, second, just need to copy and paste this down.
2:23 So we're going to do equals rejects, which is regular expressions replace. And what are we replacing? All the texts we're replacing a C 10 here.
2:32 Uh, the regular expression is going to be a little wonky. Uh, what we need to do is we do need to use the pipe.
2:39 Um, but if we just replaced that with a new line, um, we get this because in re rejects, the pipe is a special character.
2:52 So we need to, uh, escape it, which means the other way, um, this Which means we have to hit this backslash affords us just before it and say, no, what we actually mean is the pipe we don't mean, or in rejects, you can use a pipe to meet, or, um, we don't mean, or we mean this pipe, this exact text we want replaced with a new line.
3:17 Um, and that's how you do rejects replace. So we have two ways. We have joined split, which you can do here.
3:24 We have rejects replace as well. Uh, you can make a change of taking some character like pipe and moving it into a, um, vertical, uh, list.
3:38 Hopefully this helps you out.