In this video, you’ll see 3 ways to “split” data in Excel:
- Text to columns
- Textsplit
- Textbefore and textafter
Then, you’ll learn how to use =TEXTBEFORE and =TEXTAFTER:
Download the Excel File
And practice yourself: https://depictdatastudio.kit.com/textbeforetextafter
Transcript
[00:00:00] In this video, you’re gonna practice text before and text after, which are methods of splitting.
And I want you to practice this along with me.
So look down below this YouTube video in the description, and you’ll see a link to download this for free.
Okay, let me give you some context about the project and then we will get into the actual text before and text after functions.
Recently I was working on a project that looked like this, where I had, you know, a bunch of data. I had things like ID numbers, I had country codes, and I wanted to find country names, and I was gonna fill them in with good old lookup formulas, which are beyond the scope of this video. But, you know, with like a V, H, X, or index match to fill them in.
I needed to find a list of the codes and the names. I went to like good old copilot and I just asked it. I was like, “Hey, this is what I need. I need country names, I need country codes.” And within seconds it gave me the list.
I [00:01:00] tried copying it and it gave me like all this messed stuff: Afghanistan space, Space, space, space, space, space, space, pipe, space, af.
And a lot of times people are like, “Ann, like just type it in by hand,” et cetera. I don’t have time to do that for like 50 bajillion countries and codes. That’s where splitting formulas come to the rescue.
There’s different types of splitting. This is not an exhaustive list. These are just the ones that are the most related.
I tried to put some notes here just as like a quick, you know, a quick cheat sheet for you about the versions and pros and cons.
There’s text to columns. Available in all the versions, it’s buttons. It’s a wizard. It’s great, unless you are using uppercase T Excel Tables. It has to be done manually. I don’t love this. That means Future You, Future Ann has to spend more time doing it over and over and over.
Text split, amazing, but it’s not in all the versions of Excel. It doesn’t [00:02:00] work with Tables. It spills into nearby cells, which can like be a tricky thing to work around.
So enter text before and text after, which we’re focusing on today.
They’ve been around for a while, so a lot of your coworkers and colleagues that you share files with probably have them at this point.
They are formulas, which means Future you can easily replicate this. And they don’t spill, which is amazing.
Okay, let’s get into the how tos. I’ll zoom way in. I’ll demo and then remember, you should download this spreadsheet and try it yourself.
The goal is to have country name and then over here in a separate column, the country code.
Text before is gonna grab this whole cell, that whole text, comma, the delimiter, like what is it that separates it?
Well, it’s a pipe which is on my keyboard. It’s between backspace and enter is what that little symbol is. [00:03:00]
Now A3 is a cell reference. You can see the color codes. It’s like a specific location, so I don’t need any quotes around it. But the pipe is not a cell reference, so I have to surround it with the double quotes because it’s like things I’m selecting off of my keyboard.
Okay, so this is gonna grab everything before the pipe.
Country code comes after. Okay, text after. I wanna split out this cell. That’s all smooshed together. And the delimiter is the pipe, which again goes inside double quotes, as you know. Mm-hmm. Okay. So you get Afghanistan AF, and then you can fill these all the way down and they work for you. Okay.
One thing I am curious about though, remember how it’s like Afghanistan, space, space, space, space, space. I feel like to like really sleep well at night, I would probably wanna trim off the extra spaces off both of these, just to be like extra sure. I’m probably going to onion layer them, nest them inside a trim.
[00:04:00] Then I can know they’re like completely, completely perfect.
Have fun playing around with text before and text after. I love these time savers and I hope you do too.
Leave a Reply