Hey there stranger!

Sign up to get access.

How Does Split Work?

About this Tutorial

We go deep into the syntax and usage of the SPLIT() formula.

Featured Formulas

Video Transcript

 Hi. I had some questions recently about how does split work or actually what we were doing. We were trying to figure out how to get sort of the first part or second part, or third part of a text field, and I looked at that immediately and I saw sort of a common delineator or something, or rather something that was in the middle of all of this text that could be split, that could be taken into you take the first part, second part or third part.

And how we do that is say you have an. Address. Every email address is going to have an ad no matter what comes before it or what comes after it could be, and plus could be, you know, maybe somebody signs up for your newsletter and they sign it up like this, right? google.com. No matter what comes before or comes after the ad, there's only one ad in an email.

And how we split that is we go equal split. And what, what are we gonna split here? It's gonna be the text and then eliminator or what do we unlimit  limiter. It's the, the thing in the middle, let's say. And so what we're gonna do is we're gonna split, let's do split this AEC Google, and we're gonna delineate.

They're deline, delineate it, bifurcate it, whatever. We're gonna split it and we're gonna split it around the at and now in one column. The first column in every single one of these, the first column is going to be the name. I do this a lot because I run a newsletter and I like to see it's like a B2B newsletter, and I like to see what companies have a lot of people that subscribe.

And so what I'll do is I'll, I'll, I'll split this ad and get the domain only so. , this could be if we had done this maybe, right? We do that and now the split doesn't work because it's not an app. So all we do is go into here, change this to app, and now we have exactly the same thing. We are all, we're doing, oh god, this, oh, this is a really fun part.

Okay, this is really funny. This is actually characters and it thinks that like these are part of it. Oh God. So what's going on here is that if the de the texa, were splitting it by the Delmer, is there It absolutely like knows it's. But sometimes it tries to like guess. And what we have to do is we have to add in false, false.

These are options at the end. And if we do that now, it's actually going to be fine. So this is a really funny thing. If you, if you notice that happens, you can use these extra val options here. So most of the time you'll want to do false. But sometimes you want to. False and true for some reason, right?

These are both true. See, it, it kills that. A and it thinks the A is what you're trying to, to do. But no, we're gonna do false. False. And then what this does is it, it doesn't actually cut it more and more or split it more and more. Again, we can, we can almost use anything we want here. We can use a space kind of thing here.

maybe it's a hyphen with a space. And we're gonna use a hyphen with two spaces around it and see, now Anne's the same. Let's do this. And because each one of these has a space and a hyphen, it, it cuts it or splits it equally. Now in my case, at least when I was trying to get a domain, I'm only going to, I only care about this second.

Okay. In fact, we, you can absolutely go split one, split two, and you can have these as data in your formula or in your sheet. But we don't need it. We don't need the first part at all, at least in some respects, in some use cases. And if we want to we can use the index function to get around this.

We go index one. . Okay, so we go index, and we're going to go index on this. We're gonna, and the first one is the, we're, we're always gonna put one, and the second is the column. If we did one, one, we would get an right because that is, this is like a little bit of a, This is what would you call a table in itself?

It's a one row and two columns. First column is Carl, or the first name, and the second column is this do domain. So one, one gets us an but one, two gets us the domain. So if we copy and paste that all the way down, you see that it's now all of the domains we. . Okay. And so this is a pretty common use case that I have of using split and then index to grab the actual thing I want.

So if this was, say let, I'm gonna do one more thing. I'm gonna add some more data here and split it more than two rows or two columns. One sec. Okay. Say we were inputting some data from somewhere else, right? We're grabbing it. Maybe we're copy and pasting from a CSV file. There, there's some other ways to deal with CSU files, but we find that, like we have this one column with three items, and they're all comma delineated, so all we have to do is go split and we're gonna split this B2 by comma,

and we get this and we get the same all the way down. And now they're in separate columns. . But what if we're like, well, we, we don't care about anything. We don't care about the cost. It's maybe it's an old thing. We we're just like reopening all these statuses. We only care about the lead. We would do then index one, one.

We get that. But what if we wanted to do just the cost? We'd do that same thing, but at the end we'd use three and we get the cost.  and then we have texts here so we can use, we can format it any way we want, maybe as dollars. So one interesting thing too with these sort of commas is if these commas all had no comma space, it would be fine.

But if you noticed as closed has moved, so if we do comma space and now there's a comma space in between, we actually can do comma space. And we don't get that space in that status column, right? And so that's like an interesting pitfall you might run into is sometimes your eliminator might be wrong. And you'll get sort of a dirty part of the data in one of those columns.

And if you're having a, a particularly difficult time with any kind of split, let me know and I'd be more than happy to help. Try to figure it out.