Benoit Asks: How to Convert Case

<div> Hey Ben your questions were one, how can I use this function , lower, upper, or proper as a conditional formatting ? And then the other question is, how can I use this to apply on a bunch of cells without having to double the cells ? Unfortunately, there's no good answers to this. Let's look at conditional formatting first. Conditional formatting move my face over here. The formatting styles you can choose from are just fill color.  a text color strike through underlying italic or bold or all.

And then when you have, you have sort of these three options here and there's nothing else you can choose your custom formula that you chose or these what is interesting though is even in color scale , you can't do that as well. Unfortunately you can. Do that in conditional , but here's a couple ways you sort of, the answer to your second question again is not really great, but it is what I do, and then I'll give you a weird way to do that in font. So you have a list of say names and you want to lowercase them, right?

You would do lower and then you have this other column, right? Of all of this, these formulas, right? You. The only thing you can really do differently is wrap this in an array formula . So you go array Formula , formula and then put C to C there. And now you can do the entire column all in one formula . And then, By hand command C, which is copy, and then go over this and I do shift command V for paste values , so then I can get rid of this. So this is really only remembering this sort of double formula array formula lower and then DeLeT that. And so now you have it accomplished.

Programmatically you can do this with script. . There is in scripts, actually, I will show you in a second, but the only way that I can show you right now very quickly is to show that you can create a different formula . So you can say like e equals you. C1 we'll come back to this in a second. It's gonna give us an error right now. But again, you can go error a formula , you can do upper. C one or C to C, and now you can command C and shift command V for copy and paste value. So doesn't take so much time to do that.

Same with doing this proper, and we get it back to its proper place. So then command C, shift command V, and now I have my choice of any of these I can. I can copy and paste into here. We'll get back to that. We'll get back to this error over here in a second so we can delete that. And now we have proper right, so a little quicker than say going proper E one and then, you know, copy and pasting down here. , but we'll see. Maybe that helps you out. But here, how do we get this, which is even a faster sort of upper, and you can almost do anything you want. You go to tools.

Let's go to script editor . I've done this a couple times already, so you might see some scripts here already written. So I wrote this script, which is just a function U and E is here for. value and all we're doing is returning E two uppercase, which is the JavaScript slash Google script version. And if we go over here, we might see that already happening here. Yeah.

So you have now a quicker way to do exactly the same thing you did before upper and, and something else I'll I might be able to show you in a hot second. you can do macros and record them and edit them, but it's gonna be, it's pretty deep conversation. I don't know the exact macro you'll want to use because I don't know how often you're using this. And, and if you are saying if any of these are edited, do them again.

So I won't go too deep into this, but really all you're gonna do is say tools, macro . You're gonna record a macro and then there's ways you can edit that macro in order to continually do that. You know, maybe every night go through and do exactly what you need to do. But I can't really show you macro . Cause I don't know exactly. If you're doing the entire column , if you're saying if a bunch of ifs, like if this is this way or this is, this is this way, with your conditional formatting , you're usually. Based on some condition and what that condition is it wasn't clear.

So happy to try to answer your question deeper if you have some specific situations that you want to use this. So, so far here I said you can use array formula to sort of get a little faster. And probably some. Scripting. The other way is if you want to, this is weird. You can use fonts that are only an uppercase, sometimes this visually is appealing and also prevents people from.  entering a lowercase. For some reason, I, and I only, I can only come across uppercase.

You, you can find maybe some other fonts that are only lowercase. But these two, this one is Allegra alleg, sorry, alleg Graya font. It is a small cap font, so what happens is the upper. They're all uppercase, but the lowercase are smaller upper cases, small caps, and six caps. Font as well is an all uppercase but also small caps where you, you can see just a very tiny difference between the uppercase and lowercase and. That's it. So unfortunately, conditional formatting doesn't work for what you need.

The array formula and possibly a script is gonna increase your speed here, so you can do as you c1, let's see. Right, so that's quicker than typing upper upper c2, right? So equals U c3. . So you can save a few keystrokes there if you create a script. And you should be able to do all of, all of the different types of ones. And then, yeah, the array formula , just to be able to type one formula here for the entire column is pretty much the fastest that I, I know right now that you can do. And then, yeah, you can also try to use some all caps fonts.

Hopefully that helped you come up with some creative ways. Your problem, and if you want to describe your problem in a little bit more detail, I'm more than happy to try again at one of these solutions, probably better than the other's. Bite.</div>