In this video we're going to talk through some of the most common string manipulation methods we can
use in Visual Basic and like we did with the math functions.
I've compiled a list here of the most common manipulators we use when working with strings text using
combinations of these methods.
You can basically accomplish anything you want to regarding strings.
So talking through this reverse have string combination which uses the and signed to combine two sets
of strings and we've actually used this a couple of times in our videos leading up to this one.
So you may have seen this before in our example over here.
We are combining the test string with the ing string so it should result in one full word testing the
next function is to enter a new line just like hitting the enter key on your keyboard when you're typing
in a Word document the shortcut for this code in VBA is VBAC R L F which again we have used in previous
videos.
So in this example we can bind the test string with enter and then combine that with the ing string.
So we should see test appear on one line and even appear on a line underneath that.
We can also easily convert strings to be uppercase or entirely lowercase.
And this is really important because VBA typically pays attention to the case of a string when we're
searching or replacing various strings of text within other strings of text.
So these are functions you'll probably want to get you good use out of and they're both super simple
JS You case or L case with a text that you want to convert the reverse function is pretty unique and
there are limited scenarios where this would really be applicable but VBA is capable of reversing a
string and displaying those characters in reverse order simply by using this s t r for string reverse
and feed it.
The text that you're trying to reverse next.
We can break out bits and pieces of the string using right left and mid right left or both pretty simple.
You feed VBA the string you want to work with and then tell it how many characters you want to pull
out of that string.
So right will pull X number of characters from the right end of the string and left will pull X number
of characters from the left side of the string.
This is a little bit more complicated.
It pulls straight out of the middle of the string so we have to specify which character we want to start
pulling from.
And then how many characters we want to end up pulling.
So in this example we're starting from the second character the E and then pulling for characters total.
So E S T I A little bit simpler.
We can also use the alley and function for a length to find the total number of characters in a string.
So when you're doing some data manipulation and you're trying to pull out a particular code or something
from the middle of a long string these four functions are going to be super helpful if you have data
that has a lot of spaces in it a bunch of leading or trailing spaces we can use temp functions to eliminate
those l trim or eliminate spaces on the left the leading spaces and our trim or eliminate the spaces
on the right the trailing spaces plain trim.
We'll do both last but not least we can search for strings within strings and we can replace strings
with any strings.
And this is where the case of the string comes into play like I mentioned earlier.
It's best to make sure your strings match case being upper or lowercase because in the end string function
here for example we wouldn't find the letter the lowercase letter T in the uppercase string testing.
So in string which is denoted by I N S T R for n string starts with an input of a start number which
tells the VBA code where to start searching the string.
In this case we told it 1 which means start looking at the beginning of our string with that very first
capital T right there.
The next input is the search string.
It's what you're searching within.
So in our case we're searching within the testing string and the last input is what you're searching
for.
What is the substring that you want to find.
And in this case it is capital T.
So in string we'll return the location of the first instance it finds of that substring so it will count
in the number of characters until it hits this particular substring that you're looking for and return
that number if it returns zero.
It means that substring wasn't found in string reverse which is I N S T R R E EB 4 in string reverse
is really similar but opposite operates in the reverse direction so it doesn't require a starting place
it will search from the end of the string towards the beginning.
So in this example it will start with the G and testing and search backwards for the first t it encounters.
So even though these in string and in string Rev examples are both using the same string and substring
they will return different results and we'll see that in a second here.
You can also just straight replace strings with in strings as shown with the replace function we have
here for replace.
We identify the search string in this case we're looking within test x y z and then we tell VBA the
string that we want to replace.
So we want to get rid of the x y z and what we want to replace it with the i n g.
So I've gone ahead and I've typed all of this into VBA as an example so we'll go ahead and go to the
developer tab open up visual basic and we can see the example here altering this into a half window
and zoom out a little bit so that we can see as our results are getting filled out so I'll give you
a second to pause if you want to copy over this code.
Otherwise I want to talk through this line by line so I'll give you a second pause and that should've
been enough time to pause.
So now I'm going to step through this code line by line which I'm going to you using the debug step
into here.
So I'll go ahead and click on that.
You can also just use f eight on keyboards or hit F 8 and now if we break down these first couple of
cells here these first couple of lines the cells function here is referring to row three and column
five.
So if we come back to our excel file row three column five.
So this should show up in our results column.
Now in this case we're just combining two strings of text.
We're combining the test and the ing so we'll hit F eight and we can see that those two strings of text
were combined.
So now we have one full word testing.
The next thing we do is test out this VB CRL F which should represent a new line like hitting enter
on your keyboard so will hit at 8:00 and we can see that shown up.
How we expect we have this test on the first line like we do here.
Then we combine that with the new line and combine that with this in text on the tail end.
That's how it shows up here.
Next we're going to take this lower case testing and we're going to turn it into an uppercase word.
So we've had f eight and we can see we have an uppercase result here.
We'll reverse that logic.
We'll start with uppercase and move to lowercase had eight.
And we can see now we've got all lowercase.
So again these are going to become very important when we're dealing with in string and replace.
Down here you can go ahead and change them to be all uppercase or all lowercase to match whatever you're
searching for.
Now in this case we should see the word testing get reversed.
So we'll hit it and we can see it just straight reversed all the characters in that string.
So again limited application but it is there if you ever do encounter a need for it.
So on this particular line of code we're trying to pull the left for characters from this testing string
denoted by the left and we selected four characters over here so we would expect that this would show
t e s t.
So just the word test will hit effort to test that out and it looks like that's what we got reverse
in that logic a little bit on this line and we're pulling for characters again from the string testing
but this time we're pulling from the right of the string.
So hit at 8:00 and we can see we ended up with the further right characters the four rightmost characters.
Now this one gets a little more complicated.
We're looking in the middle of the string.
We're starting with the second character in the string which is the E and we're pulling for characters
after that so we'll hit F eight and we can see we started with the E and we pulled a total of four characters
so that checks out.
Now this line we're just going to pull the length of this string of testing so just going to count the
number of characters that appear in this string which is seven now with left trim.
We would expect all of the string all of the spaces on the left side of the string to disappear.
So we'll test that out and if I click into this cell we can see I don't have any spaces on the left
side but I do still have those spaces on the right side of my string.
Now one thing to note is that if you ever are in the middle of running your code and you come back into
your worksheet here and you click into the formula bar your code is not going to continue running.
If you try to continue running and that's because you have this formula bar active.
So in order to keep our code going we need to click out of the formula bar anywhere else and then we'll
go ahead we'll click back into our code here and we'll hit F eight again.
So now if I click on this particular cell we can see already it's offset from what we have here.
And that's because the left spaces are still there click out of that cell again and click back into
our code here.
And this is the regular trim function.
So we should see both ends.
Both the trailing and the leading spaces get trimmed off.
So we'll hit F eight and we can see here it is offset from both of these.
This has trimmed both the leading and the trailing spaces clicking back out of that cell and back into
our code.
In this particular line we've told VBA to look within this testing which is all capitals for a substring
in this case just the capital letter T.
Now we've told it to start looking in the very first character which happens to be at T.
So when we hit it we can see it returns one which means the very first T it found in this string searching
from left to right.
Starting with the first character it found those substring that we were looking for.
It found this t.
So it returned one.
Now with in string reverse it's going to start searching in the right end of the string and search towards
the beginning.
And again we're looking for this capital T.
So when we hit at 8:00 we can see this time it gave us return the number four.
And that's because this is the fourth character in this string.
And again we searched from the back to the front which is why we encountered this t instead of this
beginning t that we did with our regular in string.
Now the last thing we're going to test out is the replace function.
So we've in the replace function have provided the string our original string that we're working with.
This is the string that we're looking for.
And this is what we want to replace that with.
So in this case we should see the x y z replaced with the I N G here.
So we'll hit evade and we can see that's what happened on our form.
So had f eight one more time to end the sub.
So in this video we covered the most common string manipulators that you're going to use in Visual Basic
and you can pretty much use combinations of these functions to accomplish anything you need to accomplish
within VBA.
So now we can start to use more complex strings in our code.