00:00 Welcome, this is one of the most interesting funniest videos you might see because we are replacing a Google Sheet formula.
00:07 In this video we're going to replace the Google Sheet formula of proper with title case. This is because there is, okay, say you have a bit of text and you're like, I want a capital.
00:20 I want to capitalize this just like a title. I want to capitalize each individual word. And I use this in titles, blog posts, writing, sometimes I just want to read, re-case the sentence and it sometimes is easier to read in title case sometimes.
00:39 And, One of the most frustrating things about Google Sheets is that there is this particular formula, but it is called proper, meaning if I type in some sentence here on the left, you see an A2, A3, A4, and I use the formula proper here.
00:55 I'm using a ray formula. I'm wrapping it around so I don't have to type in each one, but here we're just going to use, We're going to do equal sign.
01:03 We're going to make this a little bit bigger. Equals, P-R-O-P-E-R. This is a function that is built in native to Google Sheets.
01:14 You just type that in. You have the text to capitalize and you type in A2, or you can type in the text here and it, Totally works.
01:22 The frustrating part about this is that it is called proper. Which is not proper. It is title case or title sentencing or title capitalization.
01:35 No matter what, you can rename this sort of. What I've done is I have created a, app script and a function inside of app script.
01:45 And I'll show you this. And what we have done is we have renamed it. The app script is totally different than the fundamental actual formula here.
01:54 But it allows us, you have a new type of capitalization. And you go equal sign title. Now look at this.
02:06 You have title. This is not normal. This does not come with Google Sheets normally. I added this and I will show you the whole process of how to do this.
02:15 We have some string which we're going to do A5 exactly like we did with that proper function. And there it is.
02:23 And this acts exactly the same proper. Now we need A5. Not abs. A5. There we go. Same exact result. You're getting the exact same result.
02:35 But it is just a much easier to remember formula that we can type in. And we don't necessarily have to remember equals P.
02:46 What is this? Does it start with a P or S? Do we need sentence case? The sentence case doesn't exist.
02:53 If we just want to say, okay, we want to take this text and turn it into a title. Just type in title.
02:59 And there we go. We are done. Alright, so. Let's see what we did to do this. We have done two things.
03:05 One, we have written an app script and two, we have created custom text before that. We've added text and I'll show you this.
03:12 So we're just going to open extensions app script. We already have it open so you can see. And if you are a better seats member, just go down below, copy this, this sheet and you can get this.
03:22 Or if you're watching this on YouTube, you could just try to copy this. But you can also become a member and get this absolutely free with your lifetime membership.
03:31 So we're adding this title case. We're going to this app script. And before I go through the text above it, I want to go through the function.
03:40 So we are literally calling function. And then we use a variable called string. This could be anything. Any words you want.
03:48 We're going to return this string and we're going to do dot replace. And we use a little bit of rejects here to say where some where the where the actual words with these like spaces, right?
03:58 Finding spaces and finding the actual words. And then we're going to take this phone. We're going to take those. Individual things that we find this whole string and each of these individual pieces, the used words that we use, we find, we're going to take the text at character zero, which is in arrays
04:14 , which is the first item. So the very first letter of every piece of text here, we are going to uppercase.
04:22 And then we're going to. We're going to take a substring of everything from one past that all the way to the end and we're going to lowercase it.
04:29 We're just going to return the results. Now, of course, what this does is it involves a little bit of it's a little bit of problematic, right, because it's going to re title things like McDonald's, which has a capital M and a capital D.
04:42 Or if you're. Have some names that have two capitalizations. It's not going to capitalize those correctly. So like something like Mick Donald's Donald's is going with proper.
04:56 Let's see what it does with proper a six. It also does the same thing. Oh, and it also capitalized. As proper also capitalizes the S because it is a non-alpha numeric character.
05:10 Right. So let's do our new title case, a six and see what it does. It does actually title even our function.
05:19 Our custom function here is even better than the proper function. That's pretty funny. That proper has that problematic thing where it has more capitalization.
05:28 But also, we also have an error here where we have a uncapitalized lowercase D here. So, again, problematic, but proper apparently is even more problematic.
05:39 All right. Yeah, that's it. We're returning the results of all of these string replacements. That we do. And that is the whole function.
05:46 Right. Now, how did we get the function or the, to actually work like a formula, like a native formula, right?
05:56 If you've ever done custom functions before and add, you can add them to your sheet all the time. You have to type in equals some.
06:04 Custom function and you're going to always get, even if that function exists, you're going to get this underlying red. You're not going to get auto complete.
06:12 You're not going to get an error. You're actually going to get the result. But it always looks a little scary, like when you, not this error, but this underlying red.
06:20 It always looks scary when we're using this and so. Some of us might want to create some proprietary math equations or do some interesting little JavaScript function and sell that, right?
06:32 Either sell access to that script or give it away for free as a lead magnet or create a little trip wire.
06:39 And you can do that if you want to add this kind of thing. This kind of. Custom function. Do you want to add that into a Google sheet add on?
06:47 You can distribute it for free and then people don't have to see this function here. But the issue is. With a Google sheet add on.
06:55 Is there are like, I didn't make this Google sheet add on. I made actually last week I created another Google sheet add on that is adding custom functions.
07:04 But this one with title I don't think title is like a good name to have like as a custom function.
07:12 Because it is. Has many different meanings like you might want to call this like title case or something to be more clear that it is Casey the capital is a.
07:24 In case a type of case but I wanted to make it super simple for myself to just type in equals title.
07:31 I didn't want to have to do title underscore case title case this word anything like that. So I didn't really want to I chose not to create this as a Google sheet add on.
07:40 But. Or if you want to create a Google she had on, but if you. Also just want to use it in your own Google sheet, you can add this custom text this things called JS doc.
07:52 And literally it is a sort of comment a slash to asterisk and then at the end is in asterisk and another slash to sort of keep this together at the very end.
08:02 I also add this app custom function. And in here we give some description of what this does. We use app param.
08:11 You'll see this app param when I do title here. About converts text to title case. That is convert. That's this converts text to title case.
08:25 So you can also have an at return and this sort of gives it more description to this custom function. Alright, so this is all we have to do.
08:34 The really thing you only, the real thing you only have to actually delete all of that. And I think we still get the same thing title and we get exactly.
08:45 Same thing. So the minimum you need is probably a little short description because it's not gonna be too easy to read here if it's longer.
08:53 And then literally the at symbol and then the words custom function. If you say custom functions, I don't think it will work.
09:02 Let's see. Title. Right? It does not work. You can still use it. It, You can still use this. It will work.
09:14 It will ultimately work, but it won't give you that autocomplete if you misspell this at custom function. Ask custom functions.
09:22 And yeah, minimum just have a little bit of a text. Here to describe what it is. It's a bare minimum you need.
09:30 And that is adding the title case to a Google sheet, which already has this quote unquote title case called proper, but I never remember the word proper when I'm like, I want to title this case.
09:43 So I thought this video would be fun for, if you want to create custom functions too, if you actually want the title case, become a member, grab this sheet for free.
09:53 And if you are, remember, go down below at bettersheets.co. If you're watching this on bettersheets.co. Go down below and grab this sheet, get this custom function, and it has already this custom function text here for you.
10:05 It has a phone. This function already written out, and you can add this in your app script anywhere you want to add title case instead of proper case.
10:13 Alright, bye.