Excel Hour - Learn to Excel with The FP&A Guy

Foreign hello. If you can hear me, let me know. I'd love to know where you're calling from today. Really excited to get started. We'll, we'll get things going here in just a minute. Pull up the presentation. But really excited to be here with everybody today. So if you can hear me, please let me know where you're calling in from. Would love to know. And I'm going to bring up our presentation for today. We are going to be talking about text formulas, the new text formulas. Okay. If you haven't downloaded the deck, you'll want to do that. You can do, you can get it right here using that QR code from my website. And you can also just go to the fpna guy.com, go to Excel files and you'll find it there. So again, if you're out there, I see a few people out there. Let me know in the chat if you can hear me and where you're coming from. Just want to make sure everybody can hear me. So let me know. Want to make sure I'm not talking to myself there. All right, so who's going to tell me where they're coming from first before we jump into things. Perfect. We got Jake. Welcome Jake from North Carolina. Anyone else want to let us know where they're coming from? I know there's a few other people out there. Ah, there we go. We got Ashley from Idaho, my neighbor. You're just up the road. All right, so keep those coming. Here's what we're going to cover today. Quickly, just tell you who I am. If you haven't been on one of these before, then we're going to get into dynamic text functions. We're going to cover text before, text after and text split. And I'd love to know, put in the chat, if you're using any of these formulas today, just let me know. All right. And then we'll have some wrap up and key takeaways. So just a little bit about me. My name is Paul Barnhurst. My business is called the FPA Guy. I spent most of my career in FPA working for large organizations such as American Express, Solera and others. Today you can call my job Content Creation Influencer. Whatever you want. I like content creation, but a lot of people think of as kind of influencer. I have three podcasts. You can see them down at the bottom. If you've ever listened to an episode or you enjoy them, I would ask a huge favor if you could leave a rating and review. Apple Podcasts would be great. Or Spotify I'd highly appreciate it. That helps me grow and generate revenue because the more people who listen, the more it helps me with my business. So I can do these podcasts and the all these sessions that I do. All right, so that is a little bit about me. We are now just going to jump right into the Excel file. So if everybody can go with me. You should have downloaded the student file. I'm going to open up the master file and work from that and if someone can confirm, just let me know. If you have the file. You got it open, just let me know in the chat and I might tell a quick Excel joke. I have my Excel jokes here. What's the best way to ruin a spreadsheet? Does anyone know what the best way is to ruin a spreadsheet? According to this, the answer is word art. Word art is the best way to ruin a spreadsheet. All right, so real quick, I just wanted to show a resource and give a shout out. I used it to help. Thanks Jake for letting me know. Glad you got the file. Hopefully everybody else has it. If you're having problems, just let me know. But this book up up an array. If you want to learn array formulas, of which all three of these text formulas are, it's a great book to get started. Abbott Katz did a really, really good job here about a year ago. I was fortunate enough to partner with him and he did a six hour training session on array formulas. So a lot of what I've learned, a couple ideas came from this book. So shout out to him. Highly recommended if you, if you like academic type, you know, resource type, books for things. All right, so let's jump into it. We're going to start with text before and I want to start by just kind of highlighting the syntax of it. So text before and text acts Yakan, the file you'll get from my website. If you go to Excel Resources or go to Resources, Excel files will be the first file to download and you'll be able to get it right, right there. If you can't find it, let me know. But you should be able to download the student file from my website. I also emailed that out. If you're on my newsletter list this morning, it has the link there as well. So text before and text after have the exact same syntax. The only difference before them is one data is pulling data before a delimiter, the other is P pulling it after the delimiter. So they can be very useful used together. And we'll walk through several examples, but you'll See the basic syntax here. Hey, what's the text you want to use? What is your delimiter? You know, is there an instance number? So are you looking at the first space or the second or the third? There's a match mode for things that are case sensitive or insensitive. There's what's called match end. We'll show an example of that. And there's even the called if not found. What's nice is this formula is simple in that it only requires two arguments. The rest of them, all these arguments are optional. So what we're going to do is the first couple examples I'm going to walk through with you and then I'm going to. There's a couple practice exercises you're going to do on your own and I'll give you a few minutes. So what I want to show here and we'll walk through this. Yeah, I have this SKU data and you can see in every single case there is five numbers. So has numbers, it has a dash and then it has the description. So you got sku number, SKU description. If I was on say Excel 2016, I could write a formula like this. I could say left B75, right. And it's going to give me these now. But what would happen if I put a one here now? It's not going to get that one because it's only giving me the first five. So then I'd have to start to get more technical. I'll show an example in after I start to have to use mid and find and length and a bunch of things to make it dynamic. Whereas text before us, the first thing I want you to notice is because text before looks for a certain delimiter, it doesn't care how many characters are here. It's always just going to give me everything before that dash. And so you know, this is four digits, it doesn't care. If this is three digits, it doesn't care. And so how this works, let's. We'll go ahead and write the formula together is go ahead and copy with me, write in text before. And you'll notice it gives us all the steps. The first step is, hey, what's your text? Well, here's my text. Next it says, what is your delimiter? My delimiter in this case is the dash. And because it's text, I need to put quotes around it. Everything after that is optional. So for starters, we're going to go ahead and close it and see if it works. Yep, it did. The whole list that worked. But let's go through the other operations so you start to understand. This next one is instance number. So instance number is what instance of a dash. You'll notice all of these have two dashes. Last one has one dash. So let's say if I said instance two, let's see what happens, right? The only one I get an answer from is this one. So maybe I wanted all those at the end said test. Okay, I could tell that has everything before test, because I did instance two. I could do one. I could also do from the end. I could do negative one, which in this case would be. What it's going to do if I do negative one is it's going to find the first one from the end. So it's going to give me all the numbers. But in this case, because there's one here, it's going to give me everything but test. So see, negative one operates different. It operates different from one because one goes the first one, negative one, you know, two. So you could go from the beginning or the end to tell it what instance number you want, which is really helpful, really, really handy. The more complex things are getting. And if you start nesting and trying to pull stuff out. So, all right, so we've done our instance number. Then we have a mass. Sorry, we have a match mode. If I can speak case sensitive or case insensitive. In this case, it doesn't matter because a dash is neither case sensitive or insensitive. But let's say I wanted to split it by an X. So I'm going to do a capital X here. And then I'm going to say make it case sensitive. It found nothing. Because it's like, Wait, these are all lowercase, but Right. If I made this uppercase, then it should. Let's see, why is it not picking that one up? Oh, I see why I put that in the wrong. Wrong spot. This right here should have been. Sorry, let me do that again. This should have is our instance number. This is our X here. So see, it gives me everything up to the first X. But if I change this to be case sensitive, it only gives me the first one. So now we've done four of the arguments. We've done. Here's our text, here's our delimiter, here's our instance number. Here's our match mode. We can match something to the end, which I'll show later. For now, we're going to skip that. But don't match. We'll just say don't match to the end. So it doesn't do anything. And then the last one, well, what if something's not found? Maybe I need to research it if it's not following the pattern. Like maybe you're trying to figure out ones that don't have a skew data in the right format, whatever it might be. So I could put the word research, as you can see all within this one function, there is a lot more options than the old way of doing things. Right. I think you all can probably see that. You know, fairly quickly we'll go even to more examples in text after, we'll run through quite a, quite a few. But I want to give you the basics. So that's the basic function. Now what I'm going to have you guys try to do, and I want everybody to just take a couple minutes on their own in your examples. This is this. These are all grayed out and it asks you to do a question. So we'll do the first one together and you can do it two ways. You can do it using table nomenclature, which is my preference. So you get familiar with tables or if you hate writing in tables, not familiar with them at all. I've also given it to you in standard formulas. We'll do the first one together and then I want you to do the rest. So I'm going to delete this first one says use the text before function to get only the first name from the list of names in the table. So if I come over here and look at the list of names in the table, I can see all of these are first and last name with just a space. So if I want everything before, I want just the first names. Can look at this and say, oh, I want. Let me put an equals in front of that. I want everything before the first space. So I'm just going to highlight this. All I'm going to come in here and my delimiter is just a space. So all I'm doing is putting a space between my quotes and closing it. And it did it. Now what if I'm writing it without a table? Well, you know, in this case, same idea, only difference is going to be I'm referencing the cells instead of referencing the table. The disadvantage here is if I add somebody, so let's say I add Ashley to the bottom. Nothing happens there. But here, if I add Ashley and a space so that it can delimit her, she gets added to the list because it's in a table. So I'm gonna let you try to do number two, three and four on your own. Gonna give you a Couple minutes. Let me know when you're done and then we'll keep going. I always find, you know, people will get the most out of this if I actually have you practice versus me just sitting up here and rambling on. So go ahead and take a minute and try seeing if you can figure out the first few. If you can't, you can look at. You can look at the screen, but try to figure them out on your own. And if you got all three done or you're good, let me know in the chat. There's not a lot of people in on the call today, so we can quickly move on if everybody starts telling me they're. They're done. In the meantime, I'll find an Excel joke. How do you quickly take over a spreadsheet? By using control, unless you're a Mac. All right, let me know if you're done. Just put it in the chat. I'll give another minute or two. Or if you have a question, just let me know what the question is, please. All. You're gonna want all the words. In this case, it's the same because it's only capitalized. There's only one word before where airlines is capitalized, so it can just be the first. The first word before the capitalization. But. Good question, Jake. All right, I'm going to give it one more minute and then we'll. We'll move on. Keep going. If I'm going too fast, you'll have to tell me, but text after. We're going to walk through some fun examples here in a minute with it. But text after, like I said, is the exact same as text before. You can see all the same arguments. There's no difference in any of the arguments. The only difference is it's giving you something after the delimiter instead of before the delimiter. Now here I want to show kind of two. Two examples. Then we'll give you the opportunity to practice. Then we're going to show you how you can combine the two. So what I want to show. First, let me just shrink this down a little bit. So here's the new way. You can see the formula right there. Really simple. Text after, same as the other one. But look here, if I wanted to get everything after the SKU data and I wanted it to be dynamic. Look at this formula, right? I got a mid. Then I have a find, and I gotta add a plus one. Then I got the length minus defined plus one, right? That. That's a little. If you're not comfortable with Excel, that's a formula that can take a little bit of work to write. It's not really simple. Like when I'm trying to remember this, I'll often Google it, especially now that we have text after. Now it is dynamic in the sense of, hey, if I change this here, it still gets it right, you know, so it gets all the numbers right. But this is so much easier. Text after and I'm done. The same, same type of thing as before. You know, I can say instance number one. I could say instance number two, which there is no two, so I'm going to get an error. I can say negative one in this case, since there's only one, it's the same. Or I could even just leave the instance number out. You know, I can do match mode, which doesn't really apply here. Match to end since there's only one, doesn't matter. And then, you know, we could say if, if not found, add missing or whatever we want. You can even add a formula here. So if not found, you could say, hey, do text after of maybe a comma. I'll show you another way you can do that where you can add multiple. But you could start nesting it and saying, hey, for each one you don't find. Well, then look at this delimiter. Then look at that delimiter and start, you know, trying multiple. There's a better way to do it, which I'll show in a minute. So there's a simple example. Now a fun example I want to show and we. We'll do this together is we have these names and there's no way that I know of from data store where they're all in one column. I have no way to sort them by last name. Right. I want, I want Bach here, that's his last name to be number two. Then I want Jones, Grant and Vilhelm. Sure, I could, I could manually cut and paste it, but what about when I add a new one? So what I want to show is by using the text after with the sort by, I can sort this list by last name. So we're going to do that together. I'm just going to keep this here. So if I need it. So what we're going to do is I'm going to delete this and we're going to use the sort by formula, which if you haven't seen that, I've covered that one before. You can find that under my dynamic arrays. But basically a sort by allows you to give some data, then some other data you want to sort it by. So our Data is the table. But for the array, we want to sort it by the last name. So we want to do all the text that comes after the last space. So we can do negative one and then we want it in descending order. So I'll walk through this again. Or that's descending. I want it in ascending order. So you could see it. Now did Barnhurst, Bach, Grant, Jones, Wilhelm. So let's walk through that one one more time. So basically all we're doing is we're saying, hey, here's the data we want you to sort, but we want you to sort it by the last name only. We're just creating the last name dynamically in the formula. So we're doing a formula that says text after. We're taking this as our text. We're saying our delimiter is the space. We want negative one for the last one. Then we just tell it what we want it to sort by. So it's the equivalent. If we wanted to do this as helper columns, what I would have is I would create a helper column here that's equal. We'll do it one over so it doesn't become part of the table. Equals text after this space minus one and. Oops, close that. Right, and so there's that list. I could replace this whole part right here with this list right here. I get the exact same thing. So here I've just created a helper column. What I was doing before is I was doing it in the formula. So. Right. This is just a list that's in memory, not in the grid. You can see it's the same as that list there. All right, I'm going to pause for a minute before we keep going. Any questions so far on text before, text after? Anyone seeing use cases where they could use them, I'd love to to know, so I'll give it a second. Questions? Comments? I'll do an Excel joke. We. While we're waiting, does anyone know what happens when a bar chart works out? When a bar chart works out, it becomes stacked. All right, so number three. Yes, Jake. Sorry, I. You're probably a little delay. So I didn't see it till after I had already said what it was, but Jake got it right. He's probably heard it from me before or he knows his Excel jokes. So now I'm going to have you go ahead and practice text after. There are four examples here. They're similar to text before, but you're just doing it for text after. This will really get you familiar. Then we're going to show what I think is some a fun use case of using both text before and text after to together. And so I'm going to give you probably three, four minutes to just work through these on your own, practice them, and then we'll get going. I'm going to grab me some water real quick. So just take a couple minutes and try practicing the text after. If you need me to repeat anything, you have any questions, let me know and we'll keep going here in a minute. All right, I'm back. If you're done, just let me know in the chat. But I'll give you another minute or two to just practice these exercises before we move on. All right, let's see. Does anyone know why accountants like it when you make fun of them? Because they are now allowed to deduct jokes made at their expense. All right, one more minute and then we're gonna, we're gonna keep going. All right, so what we're, what we're going to do now is we want to take this website list and get just the domain name out of it. So we're going to break it apart. There's basically three steps to this formula. We're going to do step one, two, and three in helper columns. Then we'll do them all together in the domain name as one step. So, you know, the first thing I needed to get, and it doesn't, doesn't matter which one, but I basically need to get everything either after or after the first dot or everything before the last dot. So I could start with the text before or text after. Either one will work. We'll say we'll go text after instead of before. I'm gonna. Oops, I'm gonna high. If I can type. I'm gonna highlight this whole range here. I'm going to do a dot and I want all the text after the first instance going to close it. And so now I have Google do and I have the doom. Right, you can all see that I got facebook.com. now the next step is I need to get rid of that dot. So what I can do is I can say give me all the text before this here. And I can say E7 pound that pound says give me all the records that are tied to that dynamic formula dot. And in this case, since they all have one dot, I don't even need to give it an instance number. Now I have the names. Now the one thing you'll notice is missing is these were all capitalized. I put them in proper order. So the last step would Be to wrap it in the word proper. You know, if you ever deal with names a lot, these type of, you know, formulas can be really helpful. Or if you get tag text that's kind of stuck together and you need to pull it apart. Th. Those are probably the most common use cases. Right. And so now let's go through and we're going to do this all as one formula. So we're going to start with our text after dot and one. Okay, so we got that. Now we need to wrap this in a text before and again we need the dot. So there we go. We got Google. I'm going to change this. So we're selecting all of them, not just one. So we'll do that. So now I've got to. Here. So the last thing I need to do is wrap it in a proper. So not there, here. Sorry. Wrap this in a proper. And now I have. I have it together. Now what I want to highlight with this is this is a great way to troubleshoot and to figure out formulas. Do helper columns, break it apart one at a time. And then if you don't have space for the helper columns or you can't use them, then combine the formula together. It's much easier, especially the first time versus trying to do it all in one formula in one function that first time. So break it apart. There's one formula there, one formula there, one formula there. I combine them all together into one, one big function. All right, before I go on to the next example, any, any questions? All right, we got a quiet group today. I hope that means everybody's paying close attention and learning. And if not, well, okay, so here, I just want you to see, we're going to use both text before and text after to get the first, middle and last name in separate columns. So we're going to show what we need to do in each of these to get them in separate columns. So the first thing I would do, text before. Here we're just going to highlight this very similar to what we did before. And there we have the first name. Now let's do the last name. Last name is going to be text after the negative one. Now the question is, how do we get the middle names? Only a couple of them have a middle name. So what we have to do first is we have to say, give me the text before the last space. So if I do that, I got this. Now I need the text after the first space and that will give me only the middle name. So now I can do text after. But instead of even. Instead of saying instance one, what I'm going to do here is I'm going to use this match to the end. That way these show up as blanks. And we get Nora and Joy. So it looked at that and said, okay, only match those where there's that extra where there's actually something after the space. And so here we just split out into separate columns. You know, the first, middle, and last name. Maybe all we really need is the first. Maybe we need a last name, you know, and a comma first name. Maybe that's the format we need it in. So we could do that. Maybe we want a space between first and last. Right. And so we've broke it apart. That allows us to easily. And we'll do it all as one formula. There we go. Put it all back together. All right, so that covers text before text after kind of using them together. Any examples? Anyone thinking of, hey, I would love to know how to do this with it or I'm going to give you a minute to. Just a couple minutes to ask questions and then we'll go to text split and we'll probably wrap up a little early today. I just want to do these three text functions together. At the end, I'll give a chance for anyone to ask questions on any subject. But any, Any questions so far? Anything anyone wants me to re. Go over or is everything making sense? Let me know in the chat. And in the meantime, I will tell a joke. What happens when spreadsheets break up? Because. Good question. I forgot to put a pound here. So let me show you. That's a great question, Jacob, but let me. Where was I on the. Yeah, they become Excels when they break up. So what I did, I didn't even realize I did it. So you'll notice here, I said give me F38. F38 is last name. And then on the other one, I said give me all the names in the array because I did a pound. So what that did is it gave me this, this one right here for all the last names, but it gave me each individual first name. So if I, if I flip that here, it's going to give me all the last names, but it's going to give me Paul for the first name. You have to make sure you put the pound after. And you can see it here. The pound says be dynamic. So that was a mistake. When I wrote the formula, I didn't even qu. Catch it. So thank you, Jake. I'm glad you caught that. There might be some use cases where maybe you Only want the first and something. Maybe that's why you're splitting apart and you want something different for everything else. So you could do something where you use the pound in one case and you don't in the other. In this case, that was 100% an accident. But I'm going to drink some water so I don't lose my voice. All right. Text split. What makes text split unique is with text split, you can split things into columns and into rows. So you have a delimiter that's different for columns than you do rows. You have an ignore empty, a match mode, and what's called a pad width. And we'll walk through each of those. But this is, you know, a lot of times people will just go to data text to columns, and that's probably sufficient most of the time. But there are definitely some examples where you'd use this. So we're going to start and we're going to walk through probably five examples together before you do a couple exercises. So we're going to do the first one here and I want you guys to notice something. So if I type text split here, I'm going to highlight all the. All the records and I want you to notice what happened. So now I want to split by column, which is a space, and I'm not going to put anything in for a row delimiter. Notice it didn't split them. Right. It just gave me the first name. The reason for that is text split cannot operate on multiple cells because it has both the column and row delimiter. And it gets confused. It doesn't know where to split to the next row, basically. And so you're going to want to do one reference at a time. So now we're going to say column delimiter is going to be a space. And so notice here, right now you can see it put this name over in a column, put a blank one there. I'll explain that in a minute. But if we drag this down. Let's see, drag. Let me just copy it down. If we copy the formula down. Notice there we had an issue. And here. The reason is, is there's a bunch of spaces in here. So for every single space, it's going over another column. We don't ideally want that. So how could we manage that? Well, we don't have a row delimiter, so we'll skip that. Try this. Ignore empty cells. We put true and now we drag it down. It works. What it does is it says, okay, I'm going to use the first delimiter if there's more than one delimiter right next to each other, I'm going to ignore everything after the first delimiter. Later I'll show you a way to get around the fact that you can't do this all as one formula. There is a way you can, but you have to use other formulas than just text split to get around the fact it doesn't like you selecting multiple rows at once. So we'll show you in a minute how you get around that. Second, now we're going to do one both by column n row. So here, let's say they gave me a bunch of data and they gave me the city a space, the population, a comma, city, space, population. And it just continued. I want to take that data and I want to split it out. So. So all I would have to do here is we're going to split the data two ways. First we're going to split it by column. The column is going to be a space, so bring that back, quote, space, quote. And then we want to split it to a new row every time we have a comma. So we'll just do comma, we'll close it. And notice we now got Boston, Fredericksburg, Ranchera, Toronto here we'll go ahead and we're going to copy the same formula down and see what happens. If I copy it, notice I get a big huge error because what it's done is it said, oh Boston, that and then every single space here all the way over, you see Fredericksburg way down at the end. Then it comes to the next one because of all those extra spaces. So we'll come in here, we'll type true, we'll ignore match mode pad width doesn't really matter here. We could do it without as well. But we could put in. We can do that. And now we have fixed it so it looks the same as here. And then the last example I want to show is, is really quite pretty simple is if you get a bunch of data where you just need to split it apart and it's all the same. You know, if you didn't want to do text to columns, maybe you're going to get the data updated and you're going to have to keep splitting it apart. You can come here and split it all by comma and notice it Split it right 1, 2, 3, 4, 5, 6, 7, 8 rows. So now we'll show a couple a little bit more unique use cases. But that's the general of text split. Just pull that data apart by rows or Columns. All right, this is a silly example, but it's gonna. I use, I use chat GPT to write a poem about FPNA and budgets. So since I'm the FPA guy and notice it's on different lines. So there's 1, 2, 3, 3, 4, 5, 6 lines. So what we're going to do is we're going to do two things. First, we want to be able to split every single word by multiple delimiters. So you can see it's into all separate lines. And we want it all to be on one row. So what we're going to do is first if we look at this, we can see, okay, that's, that's one delimiter is a comma, there's a second delimiter. Third, there's a period. Fourth, there's a dash. Fifth, there's a question mark. And sixth, there's this right here. So what we can do, and we going to do this kind of one step at a time, we're going to break it apart is we're first. Let me just copy this down here in case you need to look at it. We're first going to do the text split. So what I want to show you here is if I take just this row and I want multiple delimiters, I can put it in brackets and I can do multiple delimiters. I could add the period, the semicolon, the question mark, the dash. And I think this is a double dash. So I'm gonna. And then I close it. And so what that's done is you can see that just split just the first row. But I'm gonna make one change here. I want this one here. Make sure I have the right, right one. So I'm going to copy this. Oops. Copy this, not cut it. And we'll come up here and we'll add it right here. So what we'll do is we'll delete this one, we'll replace it. There we go. So now what I want to do is since these are all in separate rows and I don't want to drag them down, I need a way to combine all these rows together. So I've showed you how to split it. But the way to combine these rows together is a text join. So we'll do a text join first. And what we're going to do is the first thing a text join asks for is your delimiter. In this case, we don't want a delimiter. So we're going to do that. We're going to Say ignore empty cells. And we're going to combine all this together. So now we have everything in one cell, right? If I expand that out, all six are in one cell. Now I just have to split them apart. So to split them apart, I would just need to do text split. This is my text and then I'm splitting it by by column. So now I do all my delimiters, but instead of manually entering them all in, I can put it in a table and that will treat it the same. So now I can say no row delimiter. Hey, true for not. I don't need to put that in quotes. True for ignore empty. And notice I just took this. That had six different delimiters and I split them all apart into separate rows. Down here I did the same thing, but in this case I just split it into columns and instead of typing in each delimiter, I used a table. That way. Let's just say. Actually, let's come up here. Say for whatever reason, they have an exclamation point here. I could then add an exclamation point down here and get it to split by that. Right? So if I add a slash here. Oh, it didn't. I didn't know what to do. But if now I add the slash. Okay, Y slash. You can see it. Split it. So table is really helpful because you could do a whole list of delimiters. If you're dealing with some really messy data that you get sometimes that can be incredible. Incredibly helpful. If you're getting some really messy data, you probably want to use the reg X expressions, which allow you a lot more flexibility of pulling things apart. But you could start with text split. So now we just walk through six examples of text split. I'm going to give you a couple minutes to just try a couple exercises. I'll probably walk through the last one together. The first two should be really simple. I think number three should be simple. Some of you may struggle with four because I don't think we've covered pad width, but I want to let you go ahead and give them a try and then we'll walk through number four together and then we'll wrap up. So take two, three minutes and just go ahead and practice those exercises. If you have questions, please let me know in the chat. And I'm curious. I haven't seen any comments today. Come in from LinkedIn. Is there anyone out there in Lincoln Land today? Hey, you're welcome. Good luck. Dasian, glad you joined us or Declan. All right, we'll give you just one Two more minutes and then I'll walk through number four and then I'll wrap up. All right, so the first two, I'll just real quick. If you didn't do them, you could see the first one, it was just a simple text split with the dash. We're. We're splitting all the fruit into columns. Second one, same thing, except for we just split it into rows. So you had to add it a comma number three. You're trying case, different case sensitivity modes. You know what happens if this is true or false? Does it change? You know what's get split by what? And so you can see here, it's case case sensitive. It obviously is different than if it's case insensitive because there are a couple cases where it's capital, there's some that are lower. The last example, and this is the one wanted to spend a minute on notice here, I have this data where it says Jane equals 54. Pete has no test score. Fred equals 88, Jack equals 64. And then we have Joe. So if I come down here and I say text split, I come up here and I first want to split by the equals. So I want to go to a new column. I'm going to go equals. Then I want to split by the common. I'm going to close it. Notice I get these N A's for Pete and for Joe. I don't want an N A. So we'll say we'll ignore that one. We're not going to do anything for empty cells. We're not going to do anything for case sensitive. So we'll come to the last one where it says pad width. That's the equivalent of if it's missing or, you know, if error. It's just. It uses pad width here, a little more technical term. And we could type in what we want it to display. So it's going to tell us a Pete score is missing. So what that will do is if I do an average here, all right, see, it's going to give me an error. But if I put a zero here, like, let's just, you know, right. If I did a zero instead, that's going to tell me, oh, there's something going on. Whereas if I did a zero here, so I just said, hey, give me zero, then you're going to have an issue with your average. And I think, did I get off one? Yeah, there we go. Right. So your average would be an issue with the zero. All right, so that, that covers that. I want to go back to the PowerPoint deck for a minute. Wrong deck. Give me one second here. There we go. Foreign. And so as I mentioned, we covered the three formulas. There's a little bit about me. Here's some more resources. Microsoft has a page for all of these functions that go into detail. As I mentioned at the beginning, Up, Up. An array is a great resource. It has a chapter on all these different types of functions. Text split, text before, text after, and then, you know, kind of last but not least, just want to remind people that I offer Excel training. And the big one is I have a course called Modern Excel Course. The idea is to help you get started so you can go from this working all night to this getting to play with the kids, so to speak. Right? We all want that. Gotta love AI. All right, so for anyone who attended the webinar, anyone who's interested, I have my Modern Excel course. It's over five hours of video, has all the files, get lifetime access. When I add new lessons, you get access to those as well. If you use the word webinar, you can get 30% off the regular price of 59.99. Now you can see here a testimony from Deepak about that course. Paul's approach is fantastic. His ability to educate on key principles and new formulas to help structure your data in the most efficient way is incredible. Had the opportunity to train his whole team and help him with some of their models. And then for anyone interested in a more broad course, there's the ultimate course bundle. That one includes over 10 hours of content. In addition to Modern Excel, you get some other courses. That one's also 30% off. So anyone's interested, you can find those on my website as well. You can scan those QR codes, but just always want to share, that is. You know, I love doing these webinars, but I also at some point have to sell my courses and make a little money here and there. So that, that is basically what I had to cover for today. Thank you for attending. I'll hang on for a couple minutes in case anyone has any questions that they want to ask. But that is all I had to cover for today. And yes, I'm, I'm, I am spotting a vendor's hat. I have a lot of vendor hats, so I figured I'd give a fincore shout out today that I've worked with previously. All right, I'll give it. Just. We'll hang on here till 2, 3 minutes to see if there's any questions anybody has. Good. I'm glad you found it helpful, Ashley. And that you'll use them other. Anything else? Hey, Jake, glad you found it helpful. All right, it's 255. I'll hang on here for two more minutes and then I'm gonna hang up. Thank you, Gary. Thanks for joining. And if anyone has suggestions of what they'd like to see for a future session, let me know. I'm always looking for good ideas, so if there's something you want me to cover, throw it in the chat and I might surprise you. All right, I'm gonna hang on for about another 30 seconds and then I'll let everybody go. Activity based costing. You know, I didn't work in manufacturing. I didn't have to do activity based costing. I'm gonna leave that one to you, Gary. So. All right, well, thank you, everyone. You have a great afternoon, morning, evening, depending on where you are, great rest of your day and we'll see you again in a few weeks. Thanks, everyone. Bye. Microsoft has a page for all of these functions that go into detail. As I mentioned at the beginning, up up. An array is a great resource. It has a chapter on all these different types of functions. Text split.