The 14 Must-Know String Manipulation Functions In VBA

the ability to work with and manipulate strings in VBA is a core element of development hi this is Randy with Excel for Freelancers and today in this VBA basic training I'm going to go over everything you need to know about string manipulation and how you can work with them in VBA and I'm going to include this incredible cheat sheet to help you along the way so we're going to get started right away on this training if you do like these trainings don't forget please go ahead and like the video subscribe click the notification icon and don't forget to comment below as I respond to each and every comment these trainings are for you I do create these both comprehensive application development every single Tuesday and VBA basic training every single Saturday so I'm happy to hear your ideas feedback and everything else go ahead and put that in the comments or feel free to email me at Randy Exel forf Freelancers where I bring you additional trainings each and every week based on your ideas feedback and comments patreon is a great way to support this channel for just a few dollar a month and get a ton of value in return so check that out I'll include the links down below all right let's get started on this training string manipulation in VBA is what we're going to be working with how do we take a string and turn it around in fact string manipulation is something that I've probably done in almost every single application that I've ever developed in some sort of a way I've had to manipulate strings and so it's a really core feature and a core skill that you'll want to do when developing these applications it's something that you will want to understand and be able to work with it because when you do work with it you're able to open up your abilities and create really fantastic applications and create code in a lot less time and of course get a lot better results so let's get into it right away we're going to go into the VBA we'll go into the developers and we'll click on the Visual Basic now if you don't have the Visual Basic you can click alt f11 or you can just simply right click customize the ribbon and go ahead and make sure the developer is selected we're going to go into the developers VBA Visual Basic editor and you're going to have something like this we want to work within a module so what we're going to do is we're going to right click anywhere or simply click insert here and we're going to insert a module we're also going to be working with the immediate window in fact I just had a training on the immediate window so that'll be coming soon or already is there depending upon what order I create these videos and post them go ahead and make sure you want to view the immediate to do that you can use contrl G or select here let's get a view the immediate window so the first thing we're going to start writing some macros one fundamental is we want to know the length of a string and that's kind of important especially as a course skill working with string so let's write our first sub routine just on that we'll call this length function and inside this length function what I want to do is just Dimension a variable we'll just call it string as a string and inside that we will just populate that variable with a string called hello world now maybe I want to know the length of that string and that is going include all the letters and the spaces are all the characters we're going to use the immediate window so we're going to write in debug. print and I want to know the length so I'm going to use the length function if we take a look at that it's waiting for that expression and we can simply put in the variable here and we can get an output when we run that macro it's going to tell us that there are 11 characters inside that string and that's a very easy function to use and it's going to help us work with other functions too especially when combined we can also combine strings together we can concatenate them and so we can write something like sub string and then concatenate and to do that we can also Dimension let's say our first name as a string and we'll do last name as string and now we're going to populate those variables so first name is going to be equal to John last name is equal to do and this is pretty self-explanatory because it's simply combining the two names into a single string and there's really no function to do that it's just simply combining them even though we could use a function but it's not necessary in this so we're going to do first name and simply using the and and then we'll add a space in there and then another and and then the last name so without a function it's simply combining them using the and statement so the and will combine strings let's clear our 11 here and what we're going to do is we're going to run our macro play or F5 as a shortcut and we see that it has combined both the first name and the last name into a single string of John Doe that's great and often times we want to get some part of the string right we want to extract a portion of the string maybe the left portion or the right portion there is a function for that and we're going to call it left so the first thing what I want to do is get information about the left part of string so we're going to use left function and we want to sample on that so we're going to Dimension the string as a string variable as we're working with strings and the string is going to be equal to hello world once we have that I want to again use the debug. print and this time what I want to do is I want to get a left portion of the string so we're going to use the left function and if we see the parameters here we have the string and the length so what is the string and the length so let's say I going to put in our string variable and I want to pull in let's say just the first five characters of that and that's exactly what I want so let's say I only want the word hello I only want to get the first five characters so when we run that we see that we have just hello here and that's all we've done so we've taken the string and we've extracted the first five characters of the string we also have the right function right can be really powerful too so sub function and I'll make sure to include all these macros so you can download this for free using the links down in the description along with the cheat sheet so again once again we are going to Dimension the string as a string so we'll copy and paste that and this time our string is also going to be hello world so we can copy and paste that however we're going to use the right function so we're going to use debug. print and this time I want to use the right now the right is also the string and the length of it so let's say I want the right portion so it's going to go back from the end and count backwards in this case so the right function is going to be the string and let's say we want five here as well so we can do that and then what's that going to put out so we can simply run that macro and we see that world it's going to count backwards 5 characters 1 2 3 4 5 and the result is going to be world so we can use the right function to extract the right part of the string we can also get the middle part maybe we want a midsection of the string and we can do that using the mid function so let's write another sub routine we're going to call this mid function and the mid function has several parameters so once again we are going to Dimension our string and we're going to write in hello world as we have done before but this time what we're going to be doing is we're going to use debug do print and we're going to use the mid function and the mid function we have the string we have the starting point and then we have the length so we're going to do the string and then maybe we want to start it out let's say the second character here and then we want to know what is the length so maybe we want to put in just three letters so we're going to take a look at that so it's starting the second position and it's going to return the three characters so when we run that we see that we have let's get rid of that we see we have eell so El and here we see that it's starting at the second position and it is going to return the three characters on the mid and that's all it's going to return so the mid function is also something powerful that I use quite often we can also compare strings string compare is also a very powerful feature so we can do something like substring compare and here what we want to do is we can have two strings so we'll do Dimension string one as a string and then we'll do string two as a string as well so we have two strings now we need to define those so string one is going to be equal to let's say apple we'll do this in the lowercase and then we'll do string two is going to be equal we'll use the uppercase for the Apple so we want to be able to compare those two strings and the first one we're going to do is we're going to do debug. print so we can write in our string compare so St and then c m p then we have string one string two and then the type of comparison that we want to do so we're going to write in string one and then we're going to compare that with string two and what type of compare we have binary compare we have a database compare and we have a text compare now the text compare is not case sensitive so when we use that simply comparing the two texts if it outputs to zero that means there is no difference so let's take a quick look at that and run it let's clear this out and what we're going to do is we're going to run it so that is zero so now if I change that to Apples I just want to show you the difference it's going to show one one meaning there's a difference zero meaning there's no difference between the text that's great great for being able to compare two different text to see if they have different characters what if they're case sensitive we can also do that so let's copy this here and we'll put this down here but instead of doing VB text compare let's do VB binary compare now the binary compare is case sensitive so let's take a quick look at that and let's comment this one out just so we don't get confused and then we'll go ahead and write this down and we see it is a one so we see that it's showing a difference now just so we understand the difference if I change them both to exactly the same we're going to get a result of zero so we can use the binary compare to compare the differences between our upper and lowercase letters so that it is case sensitive so it's a great way when you want to compare two strings if they match the characters we'll use VB test compare if we want to determine if there are different cases we can use VB binary compare great so that's great for string comparison but what about if we want to use the instring function something that I've used in almost every single training is the instring so let's do that sub instring function so let's go ahead now instring is going to help us determine what position inside the string and it is going to return a long variable a number so what we're going to do again once again we are going to Dimension our string as a string because it's going to be a string and next up we'll use the hello world again hello world so inside this what I would like to know debug. print I want to know where is the O found so we want to use in string and I want the starting point of the string now this is optional so we see that the brackets around it so if it's not included it will consider to start at the beginning of the string or we can put one which is fine but it will consider that automatically what is the String we can put in the string here and what are we looking for I'm looking for the O and I want to know its position so here it's going to tell us the position of the O so when we run that macro we see that it is in the fifth position and that we're using hello notice that there's two o's here it's going to find the first one when I use inst string it's going to find the first one from left to right but what if I want to find the last one how do I do that well we can use what's called instring reverse sub instring reverse function and it's going to be very similar so let's just copy and paste what we have here and we're going to make some modifications so I'm going to paste it down here and instead of using the inst string I'm going to use reverse so let's take a quick look at the parameters on that in string reverse here is the string we want the string so we'll do string and then what we want to do is we want to know the match where are we looking I'm looking at zero so that's all that is required excuse me o not zero and so what it's going to do it's going to start at the end in inst string verse and go backwards and it's going to look for that so what it's going to return is the position of the last o in this case so let's go ahead and clear the results out we're going to run the macro and you see now it's eight it is the eighth position so it's skipped this and it's looking for last so it's starting the backwards it's telling us that the last o is in the eighth position so in string reverse can be quite helpful especially when we're working with strings with file names and we want to look for the file extension that's a good one and sometimes the file name can include periods so we would use the instring reverse to find the extension which is the last period in a full file name so that can be very very helpful especially when working with file pass and we want to extract the extensions so that's in string reverse we can also use the replacement function if we want to replace parts of a string so here we can do replace function and once again we're going to Dimension the string as string and we'll do hello world so we can simply copy that and maybe we want to replace a portion of the string with something else so we can do debug. print and then we're going to use the replace so the first part is replace is what is the expression of the string what are we looking for and then what are we replacing it with so string is what we have that's the string what are we looking for so we're going to look for world and maybe we want to replace it with VBA great so here it's going to look for the word world and so our result should be hello VBA so when we run that macro we see that we have hello VBA as a result so it's looking for the world inside this string and wherever it's found it's going to replace it with VBA replace is something that I use extremely often so it is a good function to learn we can also use trim when we have have Extra Spaces that we don't want and this is especially helpful when people working with passwords or something where you don't necessarily see a space it can be very very helpful so we're going to use the trim function so trim function and basically the trim function removes trailing and starting spaces in a string so let's do this we will copy and paste this but we're going to add some spaces onto that so I'm going to add three spaces in the beginning and I'm going to add three spaces at the end and now we are going to Simply write debug do print and we're going to use the trim function so properties of the trim are simply the string that we're going to enter and it's going to remove both the preceding and trailing spaces so we can clear this out and now what we can do is we can run our macro and we see that we've got hello world without any spaces we can also trim the spaces before or after a string as well so let's do that let's copy that here and this time what I'm going to use is L trim which is the left so we're going to do SUB L trim function so we want a sample I'm going to paste this in here we can do both the L trim and the right trim so we'll do L trim and R Trim in both so let's do that so now what we're going to do is we're going to use debug. print and then we want the L trim of the string and now let's do one more debug print and then our trim of the string great so let's go ahead and take a look at the outputs and see exactly what we have got so we're going to run this and we just run the macro here and we see that our first one Elm has removed all the spaces before however the spaces after remain and in the second sample all the before spaces still remain however the trailing spaces have been removed so the right trim removes the spaces on the right side while the L trim removes the spaces before the string very very good so those are some two nice samples here we can also do some case conversions if we want to convert cases in a string we can do that as well so subase conversion example so now we're gonna also do this in our case conversions so let's go ahead and copy this here so that we have it I'll just use this one right here we'll use the standard string that we've been working with but we want to maybe convert some of the cases on that so how would we do that well the first one is we can use the uppercase if we want to make them all uppercase so we can use debug. print we would use the U case for that so the U case simply takes the string and makes them all uppercase the second one I want to share with you is L case which makes them all lowercase and then we just use l case here and then the string variable so what that's going to do is going to take hello world it's going to first convert them all to Capital and then it's going to be all lowercase so when we run that macro we see that we have them all in capital and then all in lowercase so L case and UK case are very helpful when using that we can also process text in a larger sample so let's put a few of these things together and we can see what we can do process text example so now we're going to combine a few of them and have a look to see how they can be helpful going to Dimension the text as a string let's use text is equal to and then we'll do some spaces and then hello let's do VBA World combine a few things so now the first thing what we want to do is we want to trim the spaces so the text is going to equal to trim and then text here so first we're removing both the trailing and proceeding spaces next up what I would like to do is like to use the replace statement so we're going to see the text is equal to the replace and this time what I'm going to do is I'm going to take that text and I want to look for VBA and I want to replace it with the word excel so we can use Excel so it's going to take that and remove that oops let's fix that variable here next up what I would also like to do is to make it all uppercase so we're going to use text equals U case and then we're going to take the text so now it makes it all uppercase okay very good so once I have that I want to see what the result is so we're going to use debug do print and then text we can clear out what we have so far and we can run the macro so let's go ahead and go step by step inside this we can use our step through here step into our f8 so as we move to the macro we see that the string contains all the spaces just as we have as we trim it we see that the spaces are gone now what we're going to do is we're going to go through and we see now instead of hello VBA World it says hello text because we use the replace to replace it next up we're going to make them all uppercase so we can see now that it's all uppercase and when we debug it's hello Excel World so we've used several examples here how we can work and manipulate strings and string variables within Excel VBA a very powerful tool for programming I hope you do like this video make sure you download this particular cheat sheet which will help you if you want more macros done for you I've got an incredible 500 VBA Library that's the ultimate developers VBA Library it allows you to quickly search for any type of macro that you want and that's been a great great start and I put all of my best macros inside that so if you're looking to complete applications in one tenth the time that definitely is the product that you want I'll include the link down below thank you so much for your continued support and we'll see you next week

Channel: Excel For Freelancers

Views: 3,566

Keywords: Excel VBA, VBA In Excel, Excel Application, Excel Application Development, Excel Software, VBA in Excel, Free VBA Training, Free Excel Training, Free Excel Course, Free Excel Training Course, string manipulation, basic string manipulation, string, string tricks, excel string, vba string, strings, strings in vba, string functions, learn excel online

Id: 4ewGD3oUx8Y

Length: 18min 51sec (1131 seconds)

Published: Sat Jun 15 2024