Excel VBA Introduction Part 53.5 - Converting the Case of Text

welcome to this wise L excel vba tutorial this video is going to talk about how you convert the case of strings using excel vba code we'll start with a quick explanation of why that's important in the first place it's essentially important when you're comparing one string with another which by default in VBA is case sensitive so we'll talk about a way to make case insensitive comparisons using the option compare statement to begin with then look at a slightly more elegant way to do that using the u case L case and struck on functions you need a case by case basis well then look at how you can actually physically convert strings from whatever case they're currently in to upper case lower case and proper case will then try to replicate a bit of functionality that you might be familiar with from Microsoft Word so going to generate a custom sentence case function and then finish off by creating a custom toggle case function so a few fun things to do here let's get started okay so as usual the starting point for this video is our workbook full of data all about movies if you need to copy there'll be a link in the video description and when you've got hold of a copy we'll head straight to the VP editor I'm going to hit alpha and Dec 11 on the keyboard and then we'll insert a new module into the project I won't bother renaming the module but we'll create a quick subroutine here that's going to be called something along the lines of comparing string case and the first thing we need to do in this video is consider why it's so important to think about the case of strings when you compare them if you fold the first few videos in this series you'll be familiar with the idea that string comparisons by default in VBA are case sensitive so if I were to declare a simple string variable dim s as string and then assign a value to it I'm going to say s equals range a 17 valley and then what I'm going to do is compare the value of that variable with a specific literal string and it needs a basic if statement are going to say if s equals and then in some double quotes I'm going to type in the word Troy but you'll notice I've typed that in all lowercase letters now if that was if that comparison returns true what I would like to do is simply debug stop print the value true so nothing particularly complex now the problem of course is that if I were to run this one I'll find that nothing gets printed out at all so if I step through it you'll see that that logical test has not returned true so the that just demonstrates the fact that by default string comparisons in VBA case-sensitive which you probably know already if I were to change this so that the letter T was capital or another case lack of T and I've I've run this ability by hitting f5 you'll see that it does indeed print true now this is sort of important if you're processing a large set of data rather than just comparing one single value where you're probably pretty confident about what the case of that value is let's say for example we were looping through our list of films and we want to check for if the film was an action film so for example we could be living through the range of cells in column a and then checking with the value of the column that was a value of the cell this five column to the right was equal to the word action now in that case as we have about twelve hundred rows of data here we can't be confident that every single row that has the word action in it is spelled with the same case so in maps no pun intended we need to make sure we can compare these strings in a case insensitive fashion so let's have a quick go at doing that so to expand on this example we could declare another variable which would hold the reference to a range object so something like dim are as range and then we could say something along lines of for each R in and then I'm going to refer to range a2 down to whatever cell happens to be at the bottom of the list something we've done many times in the past so I'm going from range a to comma range a1 in dot and Excel down so that assumes a few things it assumes that there are no gaps in my list and it seems there are at least a few entries in the list in the first place to loop over so that will do for the start of our loop I'm just going to give myself a few blank lines towards the bottom and then enter the end of that loop which will be next R and then for each one of those cells that I encounter I want to check if the value of the cell that's one two three four five columns of the right contains the word action now what I can do in that case is let's say something along the lines of let's use our string variable impact let's use a string variable to retrieve the value of the cell that is offset zero come up five cells away from this one what we can then do is say if s equals action and I'm just going to spell it with a capital letter T to begin with what I would like to do is debug print the value of R such as before we do anything more traumatic like right those are these out into a separate worksheet for instance and if that's the case then all I'm going to do is head to the neck cells and we should end up with a list of action films printed to the immediate window so if I clear the contents of that and then simply run that subroutine or they ought to end up with is a list of all the action films and lists I may lose a few because the immediate window has a limit to how many films I can show but actually I've got them all so spider-man is a first action film in the list it's spelt with a capital letter a now I'm just animate the point again if any of these words any of these genres were spelled slightly differently so for instance I had the word action spelt with a lowercase a and then I head back to the VBA editor clear the contents of the immediate window and then run that subroutine again of or cysts I'm spider-man most parents of this because spider-man's genre is no longer exactly equal to the word action several different ways to make string comparisons in VBA case-insensitive one technique is to change a setting for the entire module by adding the option compare statement and stop so just below where it says option explicit we can add an option compare statement now there are several different settings here the default settings which you would never normally type in because it's City false anyway is option compared binary which means that string comparisons are case sensitive if you want to make them case insensitive then you can simply say option compare text so if I clear the contents of the image at window again by clicking into its pressing ctrl a and then deletes and then if I were to run the subroutine again even though spider-mans our genre is spelt with a lowercase letter a spider the film spider-man now appears once again at the top of the list so that's one way to make the strings case insensitive or the string comparisons case insensitive one small issue is with that is that if you were going to be copying code between different modules you might finally have different option compare settings at the top of those different modules in which case the code you've written copied might not work great intended so personally I prefer not to do this I personally prefer to make each string comparison case sensitive or insensitive as my needs require so what else could we do instead we could convert the case of the value stored in our S variable so rather than making s just equal to the value of R we could make better equal to the lowercase version of the text in that cell so we could say s equals L case which will convert a string into lowercase o in case you haven't already guessed that and then I'd need to make sure of course I was comparing the value nut string variable against the lowercase version of the word action so smokers converting the string into all lowercase techs if you're not subsequently going to compare it against poor lower string text so once again having done that even though now all of the film genres except for spider-man are spelt with a lowercase or it with an uppercase a only spider-man has has a lowercase version of the word action it doesn't matter because we're comparing like for like very lowercase to lowercase so if were to run nuts abridging at this point once again we get the entire list of action films we could do the same thing but converting from lowercase to uppercase that's not too big a leap to the imagination as a you case function but again in this case sorry again no pun intended I need to compare the value of s against all other case characters so once again just removing the contents of the immediate window running the subroutine again I will end up with exactly the same list one more time yes another way to achieve exactly the same end result is to not bother actually physically converting the case of the string you're comparing it might be important you maintain the original casing of that text but we can modify the way the if statement works so that we're not just testing if one value is exactly equal to another value we can use a function that can perform case sensitive or case insensitive comparisons so I'm going to comment out this version of the assignment and replace it with another one that's going to say if and then I'm going to press ctrl + space on the keyboard to look for a function called stroke comp or strings compare now if I open some parentheses after that you'll see there's a list of three parameters the to compulsory ones of string one and string two so I'm going to compare the value of s comment with the value or the actual literal string action it doesn't matter in this example how I spell that it can be all uppercase or lowercase or a mix of the two generally really I would never do this in the real world because it just looks horrific but you could legitimately if you wanted to spell that in any way you like as long as you have those those specific words at letters AC t io and the important thing to make this work of course is this third optional parameter compare this is kind of the equivalent of the option compare statement but rather than for the entire module it only affects this one single comparison is one single if statement so the compare mode is set by default to a binary compare which means it's case sensitive we're going to set it to be a text compare certain use VB text compare there's one further option that's not used in Excel DB database compare is only useful if you're programming in Microsoft Access so which we shall consider that at this point so do a vb text compare and then we have to check what the result of the string compare function is if it's exactly evil to zero then that means that the two strings are considered to be equal so we're using a case insensitive and insensitive comparison of both two strings so I can say if that returns zero then debug print run debug print and then we'll say we say our dot value okay so having them at let me clear the contents of the immediate window one more time and then click back into that subroutine and then run the subroutine and once again we get exactly same list of films regardless of what case the word action is in to make this procedure actually do something useful of course we want to build a list of these films in another location so perhaps into a text file or just this example of basic extra worksheet so what I'm going to do is somewhat over the top I'm going to declare a new variable which is going to be called dim WS as worksheet and then we're going to set that worksheet to refer to a brand new instance of Orochi soon as they set WS equals worksheets start ad you probably know how to do all this sort of stuff already we've covered this sort of thing in a few other videos I want to make sure that I'm the loop sorry the range of cells that I'm looping over now is specific to sheet 1 so I'm going to just modify the way I'm referencing this range of cells here by saying sheet one doctor in j-tube to sheet 1.1 by and exhale down so that makes sure that I'm looking over the range of cells on the correct sheet when I've added my new worksheet I'd also like to copy the column headings from sheet 1 into my new sheet so I'm going to say sheet 1 dot range see me range a 1 I'll get there eventually and I'm going to say I'm going to cheat a little bit I know say entire row dot copy now entire row a little bit of overkill here entire row really is every single cell on that row in the worksheet so roughly 16700 ish columns across so it's a little bit excessive but I'm going to copy that to WS range a1 so the top cell in that sheet that sheet will become the active sheet as soon as its created anyway so I'm also going to do a quick simple little cheat here and say W s so big numbers they range a to dot select so it says minutes for the starting point of copying these action films cross not necessarily the most efficient way to achieve this end result but I'm just doing this for simplicity so having a service that we're looking at an action film now what I'd like to do is rather than debug print about you bar I would simply like to perform two options I would like to say r dot entire row copy again a little bit of overkill I'd like to copy that to the active cell and the active cell will be a cell that's on the new worksheet and the saw think cell will be range a two that's one that I've just selected then other so it's a little tiny bit inefficient but rolling and then going to do is say active cell dot offset one comma zero dot select that will physically move down one row and then end if because I've added a couple of instructions to be performed as a results about conditional tests up around next cell and repeat the same action tiny little bit of tidying up at the bottom I'm going to say range a one dot select and then I am going to also say active cells dots current region dot entire column dots also fit now if I were to run this one I've got about twelve hundred rows of data to process it's going to look a little bit messy for the curling around with the screen in the background so I'm also going to do right at the very start of the procedure I'm going to say application dot screen updating Eagles false that will also improve the performance as well you don't have to update the screen for every time something changes then right down at the bottom application dot the screen updating equals true all these sorts of ideas we've done in the past but I just wanted to make something actually useful after this basic routine so having done all of that give it a quick test by hitting the run button or pressing f5 and we ought to end up with at the end a new worksheet with a list of just the action films on it regardless of what case the at word action was spelt in okay so we've seen how we can compare strings in a case insensitive fashion without actually having to physically convert the case of the strings we're comparing but what if you did want to convert the case of strings there are several legitimate reasons you might want to do that microsoft word has actually got some good examples of things you might want to do so I've actually got a really basic word document I've clearly spent this huge amount of time generating this so I thought you know need to replicate this yourself but just out of interest sake and just to dedicate the sorts of things we want to be able to do I've got the same line written out several different times here of course with that with lots of different casings of the text in those lines several ways you can modify that case as a little tool on the ribbon in words that allows you to modify the case of text so there's a lowercase option of course which is exactly the same as the yellow case function as we cuddled a quick look at that's also an uppercase version which will allow me which is exerted in the UK smoking everything gets converted to uppercase there's another one that we can do fairly easily in VBA which is called proper case or as it's describing this little menu capitalize each word so exactly so each individual word gets a capital letter as its first and then there's also another one called it's called sentence case this one's a little bit well not not quite as good as it should be actually in word we're going to create a much better version of this in VBA but study behind sentence of cases of the first letter of a sentence gets a capital letter and everything else should be lowercase so going back to the option here in choosing sentence case it works after a fashion so the middle sentence there gets the correct casing but if I wanted to do this properly for each individual sentence I have to select each one in turn which is a little bit rubbish we're going to apply to a routine that's going to do that in a much more efficient fashion checking for eventually will work I'm sure not quite working for that one for some reason either anyway we're going to create a much better version of that that does exactly what what we'd expect sentence case and there's one more as well again this is one that you can't very easily do in VBA sorry in Excel but is one that toggles the case so anything that's capital letter becomes lowercase and everything was located becomes capital so I do it you just choose a toggle case then that's what I get so everything's the opposite way around compared to the original statement so it's a bit of a silly one and the reason that one's useful if you've ever done as I have you've left the caps lock turned on while you're typing and you start typing in lowercase letters for the first word and so on and so on then that toggle case example of functions is a really handy thing to have so we're going to replicate a few of those things but writing our code in Excel VBA so I'm going to close down that documents are not about us saving the changes and then I'm going to switch back to my movies workbook I'm going to delete my action worksheets that we just created some the right-click and delete and then back to the VBA editor and let's have a brand new module to demonstrate these next couple of little things let's start by looking at the things that are quite easy to do and we kind of already know so I'm going to create a quick sublet in here calls converting case and then we'll have a simple string variable as usual they match our string I'm going to make this equal to range eight eleven value so it's got quite a lot of text in that seem yourself so quite a long film name range may eleven all you obviously where we to debug print s then we get exactly what we see in the cell so I can say D book toe print s and if I were to run that is exactly the case that you'd see in the cell so we already know how to convert strings to lowercase so I could easily do that by saying just for the sake of something a quick demonstration d book print L case s there's actually another way to do that as well as a function that allows you to modify the case of strings so rather than just always lowercase or uppercase there's a function that allows you to choose the case we may have use this in one of the previous videos actually debug dot print STR com struck on at this time not Stroh comp so we've got stricken and if I open some parentheses I can specify what string I want to convert and then I can specify what conversion I want to perform so there are some interesting sounding items in that list I might get chance to investigate some of these in a later video I think similar a little bit esoteric so I'm going to avoid things like Unicode and hiragana and katakana so not great deals dealing with Japanese characters in this particular video but if I wanted to make it to the lowercase text and there's an option that D be lower case and if I was a closer parenthesis at that point and then just run a subroutine I'll get both of these two lines converting the entire string into a lowercase letters let's just drag that across use idioms I think everything's in a lower case but the case is equally simple to achieve with these C functions I can copy those two lines and then paste those in and then of course we've seemly you case function already as you may have just spotted in this list for the stroke um function string convert function there's a V be uppercase so I can choose V be other case and then once again were right to clear the contents of the immediate window and then render the entire subroutine we'll end up with the original case and then to lower case in to upper case versions are the same line case I'd like to convert to is proper case so proper case is the case where every less L the first letter of every single word in a string gets a capital letter so let's have a quick look at how to do that if I just copy and paste these two lines we're going to use another function now sadly VBA does not have a proper case or a p case function we're always to look for a p case or proper case I won't find one in this episode proper there's not one there there's no p case function either however fortunately Excel itself does have a proper function so to access that function what we would have to do is refer to the worksheet function properties first and then inside ed as a proper function which has a single parameter which requires a string so I can say proper s and as you probably spotted earlier on there's also for the stroke cons from if you wanted to do this with VBA way then there's a VB proper case option so once again let me just comment out the locations of the cases we know what those on the TV I can just hide all those lines and then use the Edit toolbar to to comment them all out clear the contents of the immediate window and then run that one and you'll see this time that every single word in the phrase has a capital initial now VBA terms that's basically it's a functions that will convert the case of strings if we wanted to replicate the sentence case and toggle case functionality of word we have to get a little bit more creative with our expressions so let's have a quick go at generating an expression or create a basic sentence case using the exact same string this film named Harry Potter etc so I'm going to a debug print and then what we're going to do is use a couple of functions to strip apart first character and the remaining characters in the string so if I wanted to get the first character of the string you'll be familiar with the left function if you watch the the other videos in this little series here so we can take left s comma one so give me the first character from the left of the entire string I want that to be converted into uppercase so I can say you case left pest comma one and then I can concatenate that with the remaining part of the string so I can say ampersand and then to concatenate the remaining part of the string I can refer to the mid function and I want to tell it to get my characters from the string variable and begin at character position number two I don't need to specify the length in this case because I want to get every remaining two character in the string so if I omit the length parameter then that will gave me everything else from the rest of the string I want all of that to be converted into lowercase so I can say L case mid s command to wrap that up in a couple of sets of parentheses and then again I just comment out these extra two lines for the proper case clear the contents of the immediate window and then run that procedure again I end up with a sentence cased version of that film title now the small problem with this particular version of the sentence case expression is it doesn't really quite go far enough particularly if we have multiple sentences in the string so for example if I were to say s equals you know I'm just going to cheat a little tiny bit I'm going to copy how are you put on the order of the Phoenix from the immediate window and then paste that in in a set of double quotes and then I'm gonna type in a full stop and then I'm going to go and get another Harry Potter film from this this from the worksheet Harry Potter and the Goblet of Fire I'm going to copy that one head back to the VB editor and paste that on at the end and if I could quickly find another one in this list another Harry Potter film net one Harry Potter and the Prisoner of Azkaban I'm sure they were great I've never watched any of them so I'll have to take people's word for it so I'm going to paste in that final one and they're a full stop right at the very end so clear that's a string with multiple sentences and full stops followed by space and then beginning giving you sentence sadly for our function if I would clear the contents of the immediate window if I were to execute that code I don't get quite what I expect it's not exactly sentence cases it because after a full stop I should begin the next sentence of the capital letter but everything after the first character is converted to lowercase so if we wanted to generate something a little bit more complex and now our to get a little bit more creative and I think this warrants having another subroutine so let's give myself a little bit of space at the bottom we're going to create a subroutine first but eventually converting to a function let's create a sub called create sentence case and then we'll declare a couple of basic variables in here I'm going to take dim s as strings to begin with and then I'm also going to say I'm going to declare an empty array and array deck is going to contain a bunch of different strings so I'm going to say dim sentences opening closed parentheses as string so it's an empty array they can hold string values on final one I'm going to declare an integer variable I'm going to use to process a loop just as I have a string to work with I'm going to head back up to the previous routine and copy the line where I put together a whole bunch of Harry Potter films and paste up in what I'm then gonna do is split that string into multiple different sentences using a function we've seen in one of the previous videos the function is called split now if you haven't watched that video we'll cover this in a very basic detail we won't go into a huge amount of explicit detail here but essentially what I can do with my entire string is I can assign a split version of that string to an array so I've got the sentence as a rate that's currently empty I'm going to assign value see that assign a value to that or values to that using a function called split and the split function requires a complete string so I'm going to refer to my letter S or my variable S and then I can specify a delimiter for that string so a character which separates the other characters or the a of the bits of that string so the character I'm going to use in this case is going to be a full-stop character if I enter that full stop and then close the parentheses it's probably best just to step through this with the locals who know displays if I use the f8 key to begin stepping through you'll see that my s variable contains the full string to begin with but if I then execute this line by pressing f8 you'll see that my sentences array contains everything separated by . so each individual harry potter film title so what i need to do now is once i've split those into their component parts is i need to make sure that the first letter of each individual parts of the entire phrase has a capital letter and then the remaining characters in that string are lowercase letters so i can achieve that reasonably easily by using this simple for next loop and that's what this integer variable was for to process the individual sentences in the array I'm going to use a basic for next loop so I'm going to say for I equals I can't go and see how many individual sentences I will be in this array eventually so I want to make sure that it will work flexibly so let me use the l bound function to calculate the lowest bound of the sentences array and then I'm going to use the you bound function to calculates the upper bound of the array if you're not that familiar with working with arrays or an entire video that explains all about how a race work so again I'm not going to go into a massive amount of detail here but I'm going to close out lis by saying next I and then the first thing I'd like to do inside this loop is assign the current sentence to my string value so I'm basically going to overwrite my string variable with just the first sentence so I'm going to say s equals sentences and then you see counter very well the invariable to retrieve the sentence at that particular element of the array next I'd simply like to create the sentence case for that one single sentences contained in that string variables so the simplest way to do that is to cheat a little bit head back up to the previous routine and then just copy the expression that we were generating earlier on and then we can say s equals and then paste that in and then we can say sentences I equals s so once you've converted it to sentence case we'll just pass that string back into that element of the array now at that point things aren't quite as complete as they need to be let me just quickly show you why if I were to use the f8 key to begin stepping through and if I can expand my sentences array here so hopefully what you can see here you might just about be able to make it out sorry I can't make the font size of this particular part that's screamingly bigger but the first sentence will work quite basically Harry Potter and the order of the Phoenix begins with the letter H capital letter H and that will be converted into a Harry Potter the capital letter H and then all the remaining characters will be lowercase characters and that will then feed back into element 0 the sentences array then move on to the next one and it will load the next phrase which is Harry Potter on the Goblet of Fire and you me just about be able to make out this extra little space character here so the first character of this string is a space which I'm going to try to convert into an uppercase space not that you'll be able to tell that and then the remaining characters become lowercase characters which means that basically everything else after the space becomes a lowercase character and the same will be true for the next one here as well so the next one become all lowercase and then finally I've got one extra empty string actually so I've got one final empty string where the last full stop in the sentence was that's processed although it's not really necessary to process it we could eliminate that as well but more importantly we need to make sure that we get rid of this extra space at the start of the string so to do that we can use another function we haven't used this I don't think in anything little videos in this little mini series on strings there's a function called trim which will take away leading and trailing spaces from a string so you may use the trim function before in Excel this there are three different versions of the trim function in VBA does trim there's el trim which will take spaces from the left-hand side of the string and there's our trim which will take spaces from the right-hand side of a shrink I just want to get rid of any leading and trailing spaces so I'm going to say s equals trim sentences I and that will get rid of this space characters and that will solved that little particular problem if we wanted to avoid having to process the the empty string at the end it doesn't make any difference it doesn't cause any harm but if you wanted to avoid attempting to process that we could simply check to see if if the result of the value of s was an anti shrink or in fact we could do that before we attempt to assign the value to it so we could simply say here if sentences I it's not equal to an empty string then a four map set of three instructions I can select those three lines and hit the tab key to indent them all and then finally dropping the end if at the end okay so at that point we'll have ended up with the array containing all of the individual sentences happily now converted into sentence case the next job is to put them all back together again into a single continuous string the technique we're going to use to join back together all of the individual strings into a single string is another function we've used in a previous video called join so join respectively does the opposite of what split does split takes a continuous string and breaks it into an array join takes a contents of an array and joins them into a single string so you tend to use these two in conjunction with each other so let's just keep myself a blank line or two after I've finished looping I'm going to now say s equals join open some parentheses I've got through a third to my array first which is sentences and then I can specify what delimiter character I would like to use to separate the individual sentences you may have noticed from the immediate from the locals in there beg your pardon earlier on that when we split using this . the full-stop character was removed so i would like to put a full-stop character and a space in between each each individual sentence make them so sentences comment an instant approach a full-stop and this space i can then close the double quotes and close the parentheses if i can then just finally debug print the result of that CD booked a print s and then clear the contents of the immediate window run the entire subroutine i will end up with a beautifully sentence taste of version of the original string so the start first character of each sentence or the first actual letter rather than first character of each sentence becomes a capital letter and the remaining letters in that sentence become lowercase letters one tiny little additional thing you may notice you associate to be selected at the end but you see i've got a full stop and a space at the very end of the string here as well i don't want that extra space and we already know how to get rid of it so let's just add in a quick trim to trim off any leading or trailing spaces and then one more time if I were to just clear the contents of the immediate window and run that one one more time and then right at the end of my string now there's no extra space you can see there's no extra space at the end now although this is working reasonably well already as a subroutine it would be much more useful as a function that we could call and pass any string into it to return the sentence case version so rather than using a subroutine for this what we're going to do is convert it into a function quick simple way to do that if you don't care about keeping the original subroutine and if you do care about keeping the originals just make a copy of this I'm going to convert my existing submitting into a function by changing the words sub to a function and then you'll see that if I click away from that line it creates the word end function rather than end sub I'm going to modify the name of the function so it's not called create a sentence case it just called sentence case I want to tell the something that is going to return a string so the single distinguishing feature that separates functions from subroutines is a function can return a value you probably know this already we've watched earlier videos in this series long as a function sentence case as a string so the result of this function will be of course a shrink when I want my function to return something I can simply state the name of the function and then state what path you it will return so rather than at the bottom saying debug print s I'm going to say sentence case equals s and that will be the value that the function returns it will also be immensely useful if I can actually pass a value into the function so currently it's just going to converge this original phrase Harry Potter blah blah blah so what I'm going to do is provide a parameter to this function so I'm going to move my barrel without the word dim I'm just going to move s as string inside the parenthesis there so that the class a parameter but I can now pass arguments into to modify what the function does each time I'm going to get rid of the remaining dim at the top there and I'm also going to get rid of the line which sets about U of S because that will now be passed in by a different different function calls so that's all that we needed to do to convert it into a function and the really neat thing about this is that we can call that function from a variety of different places which is clear the in patch and yes I'll clear the contents of the immediate window beg your pardon if I type in a question mark into the immediate window just to test my function press control space look for sentence case open some parentheses I can type in some double quotes and then type in some silly text or anything you like making sure you put full stops to separate individual things you can do random checks like so it really doesn't matter as long as you put in a few double few full stops or periods to make sure that you separate out sentences close double quotes and close the parentheses and then hit enter what you ought to end up with is the same string returned back to you but in sentence case the other quite nice thing about creating user-defined functions although they're not the most efficient things in the world they're great for custom calculations like this you're not to use these in your worksheets as well so imagine that my list of still names is actually a list of sentences in the wrong case I could create a new calculation let's say I'm going to go ahead across the column L to avoid just messing up the data I already have here and in here I could say equals and then start typing in the name of my custom function sentence case you'll see it should appear in the intellisense list along with all the other built-in Excel functions you'll also be able to find that vegetative to escape a couple of times you'll also be able to find that in the function wizard if I choose to insert a function and then choose from the drop-down the second use user-defined and you'll see my sentence case function is sitting now so I can give that a quick double click to insert it I get a basic function arguments dialog box not particularly complicated no help available but if I just refer to a cell like a to Jurassic Park and then it's okay and I'll get the results of that Jurassic Park I could double click to fill that formula downloads and take a little while to process but you'll see eventually they all change to give me the sentence case for each individual film in my list some have got slightly longer names and others but you can see that is giving you the sentence case I'm pretty sure none of our film names actually containers full stop so there's no Oh beg pardon there I wanted to go sadly for testing purposes it's actually followed by a number so there's no real effective way to tell if it is working properly but you could easily check for that by adding in some different sentences into into column a so there you go there's a nice neat little way to encapsulate your your logic your calculation into a reusable user-defined function we've got previous videos in the series of explain how to create user-defined functions so there's a few more examples and even how to convert that those functions into an Excel add-in as well that's quite handy to be able to distribute the function I'm not going to do that in this video we've got which I'm not already earlier in the series okay so there's just one more example we'd like to try to recreate from the word string conversion functions I'd like to create a toggle case example in Excel VBA so let's set back to the VBA editor and we'll stick with the same module for this let's create a quick new subroutine just down towards the bottom called toggle case now there are a few different variables we're going to use to generate this miss example I'm going to have 2 string variables that hold both the original version of the string and the toggles or the university version of the string so I'm going say dim s1 as a string and then another s2 as a string not very impressive names I appreciate but feel free to give them more inventive names if you like I'm just doing a little tiny bit lazy the string born will be the string containing the original case and string 2 will be the inverted case what we're going to do to convert one to the other is loop over the string character by character and for each character we encounter will test if it's upper case commit to lower case and vice versa so it's not necessarily the most efficient way to achieve this results but it's the best I can think of off the top of my head so and it's a nice little demonstration of some of the strings functions we've looked at in this little mini series as well so going to have another variable it's going to keep track of each individual character I'm gonna say dim C as a string now because this variable is only ever going to contain one single character I can guarantee it's length will always always be one we can use some people looked at very early on in this film miniseries the very first video we created an introduction to strings we can make this a fixed length variable so I can multiply that by one or but in star one asterisk 1 and that will make that variable a fixed length variable a fixed length string so it can only ever contain one character but it requires less memory to process a fixed length string than a variable length string so we might as well take advantage of it while we're doing something that we can guarantee the length of one last one I'm going to declare a variable that keeps track of the number so the number of characters I'm going to say dim n as long I'm using long because you'll you'll be familiar again from the earlier video in the series the string of variable length string can contain up to two point one four seven billion characters ish and the integer datatype isn't anywhere near big enough to accept that number into your stops at 32,000 167 where as long ago as all the way up to 2 point 1 4 7 billions so that's the safest one to use for this example having said that we're going to create a very basic simple string to begin we're going to say s1 equals and then we'll insert a literal string again just for testing purposes let's do a couple of examples of application lower case and upper case again and that will probably do for now then what we're going to do is loop over the characters of that string we're going to use a for next loop to do that we're going to say for N equals 1/2 and then includes the Len function to calculate the length of s1 so the characters in a string or indexed from one so we we can guarantee that n will always the first character will always be position number one and then the last character will be whatever the length of the string is I can say next and move on to the next one and then what I would like to do at that point is read the current character into my C variable as long as I see equals mid the string I'm expecting the data from is s1 the character position I want to get is N and then the length of the string is always going to be exactly one character so I can choose the length or set the length to one quick simple logical test at this point we've got a variety of ways of doing this but I think a simple convenient ways to use our struck on functions something we've already used earlier in the video so I'm going to compare the value of C against the I'm going to go with the uppercase version of C doesn't matter if we do this in two application lower case you can test this logic either way around but the really important thing here is that we're definitely testing in a case sensitive compare mode now that's the default for any module anyway but to be absolutely sure and it's also the default of this particular method so it's a big promise that the default to the method rather than the default for the entire module so we don't absolutely need to do this but if you wanted to be explicit you wanted to make sure that people reading this understood what was going on you could put in VB binary compare so I can close the parentheses at that point if I check that that is equal to zero then I will know that that individual character is definitely an uppercase character and if that's true then what I can do is say well C equals the L case version op C so I can change it from upper to lower case otherwise it could only possibly be if it wasn't difficult it could only possibly be lower case so I can say C equals new case C so it will convert any uppercase character to lowercase and vice-versa if I stick in the end if I'm Djem what I then want to do is build up the each individual character into string too so I'm gonna set s two to be equal to whatever it currently contains and the character that I've just converted once I finished looping just to demonstrate that it works let's have a couple of debug prints so I can say debug print s1 debug print s2 and then finally if I clear the contents of the image your window and then run the entire routine I'll end up with the inverted version of my original string the final thing to do then to make that useful would be just as we did with the sentence case so between convert that into a function and we can basically fellow exactly the same steps if I can change sub to the word function and then make sure that that function returns a string I don't have to state that it will return a value without specifying is tight but it would send a very intim it's always better to be specific about the datatype if you can be we also need to declare a parameter so I'm going to use s1 as a parameter passed in so I can just click and drag to move s1 a string and then just take away that extra comma that's left trailing around there I so need to ask to our stringers our variable should really give us a better more sensible name but that would mean I'd have to change all the other s ones in here as well so I'm not going to do that at this point for us it isn't there's not that many to do but if you wanted to give it a more sensible name you can edit them in there and then just replace any other s1 apart from that one which we don't need anymore we're not going to explicitly set the value of s1 that's going to be set by passing an argument into that parameter so we can remove that line that says s1 equals ABC etc finally we don't want to debug print s1 and s2 at the end what we want to do instead is just return that as the result of the function so to do that I can say hoggle sorry not topple toggle case equals s 2 which is the inverted version of the original string so what we ought to be able to do now is if I just have a quick go up maybe having a question mark in the immediate window and saying toggle case and then I'm going to open up some round brackets and double quotes and closer double quotes in the parenthesis and then hit enter I'll get the inverted version if I do the same thing to reimburse what I've just inverted just to make sure that the whole thing works hit enter at the end that will give me the original conversion and again you can use that very of sorry that function can be used in your worksheets as well so I could for some reason if I really wanted to convert the or invert the case by saying equals toggle case and then much refer to cell a2 and then we've got too many parentheses there a two closer parentheses and that will give me the inverted version of Jurassic Park and again if I just double click to fill that one down it'll take a little while to process but eventually I'll end up with the inverted string of every individual form it look a bit stupid doesn't it this is the sort of thing you kept if you accidentally typed with the caps lock key still turned on so it would probably make more sense to invert it the other way if you end up with text like this use your toggle case function to change it back to its sensible case if you like what you've seen here why not head over to the wise our website where you can find those more free resources including these videos some written blogs and tutorials meeting some exercises that you download to practice your skills thanks for watching see you next time

Channel: WiseOwlTutorials

Views: 8,799

Rating: 5 out of 5

Keywords: excel vba, vba, excel, visual basic for applications, introduction, intro, basic, string, text, strings, capital, lower, upper, lowercase, uppercase, case, ucase, lcase, strcomp, strconv, proper, proper case, title, title case, sentence, sentence case, array, join, split, toggle case, switch case, invert case, wise owl, option compare, binary, compare text, case sensitive, case insensitive

Id: A9vzpR0BOXc

Length: 45min 35sec (2735 seconds)

Published: Fri Feb 24 2017