1 00:00:00,570 --> 00:00:05,100 In this video we're going to talk through some of the most common string manipulation methods we can 2 00:00:05,100 --> 00:00:08,700 use in Visual Basic and like we did with the math functions. 3 00:00:08,700 --> 00:00:14,490 I've compiled a list here of the most common manipulators we use when working with strings text using 4 00:00:14,490 --> 00:00:15,960 combinations of these methods. 5 00:00:15,960 --> 00:00:19,530 You can basically accomplish anything you want to regarding strings. 6 00:00:19,620 --> 00:00:25,950 So talking through this reverse have string combination which uses the and signed to combine two sets 7 00:00:25,950 --> 00:00:30,910 of strings and we've actually used this a couple of times in our videos leading up to this one. 8 00:00:31,020 --> 00:00:34,390 So you may have seen this before in our example over here. 9 00:00:34,530 --> 00:00:42,180 We are combining the test string with the ing string so it should result in one full word testing the 10 00:00:42,180 --> 00:00:47,190 next function is to enter a new line just like hitting the enter key on your keyboard when you're typing 11 00:00:47,190 --> 00:00:55,350 in a Word document the shortcut for this code in VBA is VBAC R L F which again we have used in previous 12 00:00:55,350 --> 00:00:56,280 videos. 13 00:00:56,280 --> 00:01:03,300 So in this example we can bind the test string with enter and then combine that with the ing string. 14 00:01:03,360 --> 00:01:09,630 So we should see test appear on one line and even appear on a line underneath that. 15 00:01:09,930 --> 00:01:14,100 We can also easily convert strings to be uppercase or entirely lowercase. 16 00:01:14,190 --> 00:01:19,040 And this is really important because VBA typically pays attention to the case of a string when we're 17 00:01:19,040 --> 00:01:23,420 searching or replacing various strings of text within other strings of text. 18 00:01:23,430 --> 00:01:27,840 So these are functions you'll probably want to get you good use out of and they're both super simple 19 00:01:27,840 --> 00:01:34,550 JS You case or L case with a text that you want to convert the reverse function is pretty unique and 20 00:01:34,560 --> 00:01:39,860 there are limited scenarios where this would really be applicable but VBA is capable of reversing a 21 00:01:39,860 --> 00:01:46,430 string and displaying those characters in reverse order simply by using this s t r for string reverse 22 00:01:46,750 --> 00:01:47,280 and feed it. 23 00:01:47,330 --> 00:01:49,840 The text that you're trying to reverse next. 24 00:01:49,910 --> 00:01:55,700 We can break out bits and pieces of the string using right left and mid right left or both pretty simple. 25 00:01:55,700 --> 00:02:00,230 You feed VBA the string you want to work with and then tell it how many characters you want to pull 26 00:02:00,230 --> 00:02:01,210 out of that string. 27 00:02:01,550 --> 00:02:07,520 So right will pull X number of characters from the right end of the string and left will pull X number 28 00:02:07,520 --> 00:02:10,910 of characters from the left side of the string. 29 00:02:10,910 --> 00:02:12,840 This is a little bit more complicated. 30 00:02:12,860 --> 00:02:18,080 It pulls straight out of the middle of the string so we have to specify which character we want to start 31 00:02:18,080 --> 00:02:18,800 pulling from. 32 00:02:18,830 --> 00:02:21,960 And then how many characters we want to end up pulling. 33 00:02:22,310 --> 00:02:28,430 So in this example we're starting from the second character the E and then pulling for characters total. 34 00:02:28,460 --> 00:02:32,400 So E S T I A little bit simpler. 35 00:02:32,400 --> 00:02:38,070 We can also use the alley and function for a length to find the total number of characters in a string. 36 00:02:38,070 --> 00:02:43,530 So when you're doing some data manipulation and you're trying to pull out a particular code or something 37 00:02:43,530 --> 00:02:49,760 from the middle of a long string these four functions are going to be super helpful if you have data 38 00:02:49,790 --> 00:02:55,970 that has a lot of spaces in it a bunch of leading or trailing spaces we can use temp functions to eliminate 39 00:02:55,970 --> 00:03:03,020 those l trim or eliminate spaces on the left the leading spaces and our trim or eliminate the spaces 40 00:03:03,050 --> 00:03:05,990 on the right the trailing spaces plain trim. 41 00:03:05,990 --> 00:03:12,740 We'll do both last but not least we can search for strings within strings and we can replace strings 42 00:03:12,740 --> 00:03:13,930 with any strings. 43 00:03:14,000 --> 00:03:18,470 And this is where the case of the string comes into play like I mentioned earlier. 44 00:03:18,530 --> 00:03:23,900 It's best to make sure your strings match case being upper or lowercase because in the end string function 45 00:03:23,900 --> 00:03:31,020 here for example we wouldn't find the letter the lowercase letter T in the uppercase string testing. 46 00:03:31,190 --> 00:03:40,160 So in string which is denoted by I N S T R for n string starts with an input of a start number which 47 00:03:40,160 --> 00:03:44,070 tells the VBA code where to start searching the string. 48 00:03:44,090 --> 00:03:49,280 In this case we told it 1 which means start looking at the beginning of our string with that very first 49 00:03:49,280 --> 00:03:51,320 capital T right there. 50 00:03:51,320 --> 00:03:53,410 The next input is the search string. 51 00:03:53,420 --> 00:03:55,310 It's what you're searching within. 52 00:03:55,310 --> 00:04:00,620 So in our case we're searching within the testing string and the last input is what you're searching 53 00:04:00,620 --> 00:04:01,100 for. 54 00:04:01,100 --> 00:04:03,200 What is the substring that you want to find. 55 00:04:03,200 --> 00:04:05,030 And in this case it is capital T. 56 00:04:05,660 --> 00:04:12,200 So in string we'll return the location of the first instance it finds of that substring so it will count 57 00:04:12,260 --> 00:04:17,510 in the number of characters until it hits this particular substring that you're looking for and return 58 00:04:17,510 --> 00:04:19,700 that number if it returns zero. 59 00:04:19,700 --> 00:04:29,010 It means that substring wasn't found in string reverse which is I N S T R R E EB 4 in string reverse 60 00:04:29,310 --> 00:04:35,160 is really similar but opposite operates in the reverse direction so it doesn't require a starting place 61 00:04:35,550 --> 00:04:38,800 it will search from the end of the string towards the beginning. 62 00:04:38,820 --> 00:04:45,630 So in this example it will start with the G and testing and search backwards for the first t it encounters. 63 00:04:45,630 --> 00:04:51,810 So even though these in string and in string Rev examples are both using the same string and substring 64 00:04:51,870 --> 00:04:55,260 they will return different results and we'll see that in a second here. 65 00:04:56,490 --> 00:05:02,700 You can also just straight replace strings with in strings as shown with the replace function we have 66 00:05:02,700 --> 00:05:04,290 here for replace. 67 00:05:04,290 --> 00:05:11,280 We identify the search string in this case we're looking within test x y z and then we tell VBA the 68 00:05:11,280 --> 00:05:13,070 string that we want to replace. 69 00:05:13,080 --> 00:05:19,020 So we want to get rid of the x y z and what we want to replace it with the i n g. 70 00:05:19,200 --> 00:05:23,310 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 71 00:05:23,310 --> 00:05:30,300 developer tab open up visual basic and we can see the example here altering this into a half window 72 00:05:31,720 --> 00:05:41,050 and zoom out a little bit so that we can see as our results are getting filled out so I'll give you 73 00:05:41,050 --> 00:05:44,260 a second to pause if you want to copy over this code. 74 00:05:44,320 --> 00:05:49,520 Otherwise I want to talk through this line by line so I'll give you a second pause and that should've 75 00:05:49,530 --> 00:05:50,500 been enough time to pause. 76 00:05:50,500 --> 00:05:56,320 So now I'm going to step through this code line by line which I'm going to you using the debug step 77 00:05:56,350 --> 00:05:58,030 into here. 78 00:05:58,030 --> 00:05:59,510 So I'll go ahead and click on that. 79 00:05:59,530 --> 00:06:05,530 You can also just use f eight on keyboards or hit F 8 and now if we break down these first couple of 80 00:06:05,530 --> 00:06:11,680 cells here these first couple of lines the cells function here is referring to row three and column 81 00:06:11,680 --> 00:06:12,240 five. 82 00:06:12,250 --> 00:06:17,310 So if we come back to our excel file row three column five. 83 00:06:17,340 --> 00:06:20,320 So this should show up in our results column. 84 00:06:20,380 --> 00:06:23,750 Now in this case we're just combining two strings of text. 85 00:06:23,800 --> 00:06:30,070 We're combining the test and the ing so we'll hit F eight and we can see that those two strings of text 86 00:06:30,070 --> 00:06:30,760 were combined. 87 00:06:30,760 --> 00:06:33,330 So now we have one full word testing. 88 00:06:33,760 --> 00:06:40,060 The next thing we do is test out this VB CRL F which should represent a new line like hitting enter 89 00:06:40,060 --> 00:06:44,190 on your keyboard so will hit at 8:00 and we can see that shown up. 90 00:06:44,200 --> 00:06:47,540 How we expect we have this test on the first line like we do here. 91 00:06:47,680 --> 00:06:52,950 Then we combine that with the new line and combine that with this in text on the tail end. 92 00:06:52,990 --> 00:06:55,060 That's how it shows up here. 93 00:06:55,060 --> 00:07:00,290 Next we're going to take this lower case testing and we're going to turn it into an uppercase word. 94 00:07:00,370 --> 00:07:04,600 So we've had f eight and we can see we have an uppercase result here. 95 00:07:04,600 --> 00:07:05,760 We'll reverse that logic. 96 00:07:05,760 --> 00:07:09,100 We'll start with uppercase and move to lowercase had eight. 97 00:07:09,310 --> 00:07:11,330 And we can see now we've got all lowercase. 98 00:07:11,380 --> 00:07:16,120 So again these are going to become very important when we're dealing with in string and replace. 99 00:07:16,120 --> 00:07:20,770 Down here you can go ahead and change them to be all uppercase or all lowercase to match whatever you're 100 00:07:20,770 --> 00:07:22,120 searching for. 101 00:07:22,120 --> 00:07:25,720 Now in this case we should see the word testing get reversed. 102 00:07:25,720 --> 00:07:30,990 So we'll hit it and we can see it just straight reversed all the characters in that string. 103 00:07:31,000 --> 00:07:36,100 So again limited application but it is there if you ever do encounter a need for it. 104 00:07:36,850 --> 00:07:43,060 So on this particular line of code we're trying to pull the left for characters from this testing string 105 00:07:43,210 --> 00:07:48,970 denoted by the left and we selected four characters over here so we would expect that this would show 106 00:07:49,270 --> 00:07:50,570 t e s t. 107 00:07:50,570 --> 00:07:56,200 So just the word test will hit effort to test that out and it looks like that's what we got reverse 108 00:07:56,200 --> 00:08:00,940 in that logic a little bit on this line and we're pulling for characters again from the string testing 109 00:08:01,000 --> 00:08:03,380 but this time we're pulling from the right of the string. 110 00:08:03,400 --> 00:08:10,730 So hit at 8:00 and we can see we ended up with the further right characters the four rightmost characters. 111 00:08:10,750 --> 00:08:12,720 Now this one gets a little more complicated. 112 00:08:12,730 --> 00:08:14,710 We're looking in the middle of the string. 113 00:08:14,770 --> 00:08:20,650 We're starting with the second character in the string which is the E and we're pulling for characters 114 00:08:20,680 --> 00:08:26,650 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 115 00:08:26,680 --> 00:08:28,400 so that checks out. 116 00:08:28,540 --> 00:08:33,430 Now this line we're just going to pull the length of this string of testing so just going to count the 117 00:08:33,430 --> 00:08:39,900 number of characters that appear in this string which is seven now with left trim. 118 00:08:39,920 --> 00:08:46,460 We would expect all of the string all of the spaces on the left side of the string to disappear. 119 00:08:46,460 --> 00:08:52,600 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 120 00:08:52,600 --> 00:08:56,590 side but I do still have those spaces on the right side of my string. 121 00:08:56,590 --> 00:09:02,170 Now one thing to note is that if you ever are in the middle of running your code and you come back into 122 00:09:02,170 --> 00:09:07,450 your worksheet here and you click into the formula bar your code is not going to continue running. 123 00:09:07,450 --> 00:09:11,660 If you try to continue running and that's because you have this formula bar active. 124 00:09:11,800 --> 00:09:16,990 So in order to keep our code going we need to click out of the formula bar anywhere else and then we'll 125 00:09:16,990 --> 00:09:21,460 go ahead we'll click back into our code here and we'll hit F eight again. 126 00:09:21,460 --> 00:09:26,600 So now if I click on this particular cell we can see already it's offset from what we have here. 127 00:09:26,620 --> 00:09:33,130 And that's because the left spaces are still there click out of that cell again and click back into 128 00:09:33,130 --> 00:09:34,720 our code here. 129 00:09:34,720 --> 00:09:36,520 And this is the regular trim function. 130 00:09:36,550 --> 00:09:38,320 So we should see both ends. 131 00:09:38,320 --> 00:09:41,760 Both the trailing and the leading spaces get trimmed off. 132 00:09:41,770 --> 00:09:46,520 So we'll hit F eight and we can see here it is offset from both of these. 133 00:09:46,520 --> 00:09:54,250 This has trimmed both the leading and the trailing spaces clicking back out of that cell and back into 134 00:09:54,250 --> 00:09:55,540 our code. 135 00:09:55,900 --> 00:10:03,160 In this particular line we've told VBA to look within this testing which is all capitals for a substring 136 00:10:03,220 --> 00:10:05,760 in this case just the capital letter T. 137 00:10:05,770 --> 00:10:10,360 Now we've told it to start looking in the very first character which happens to be at T. 138 00:10:10,360 --> 00:10:16,450 So when we hit it we can see it returns one which means the very first T it found in this string searching 139 00:10:16,450 --> 00:10:17,850 from left to right. 140 00:10:17,980 --> 00:10:22,680 Starting with the first character it found those substring that we were looking for. 141 00:10:22,690 --> 00:10:23,790 It found this t. 142 00:10:24,070 --> 00:10:25,840 So it returned one. 143 00:10:25,870 --> 00:10:31,900 Now with in string reverse it's going to start searching in the right end of the string and search towards 144 00:10:31,900 --> 00:10:32,890 the beginning. 145 00:10:32,890 --> 00:10:34,810 And again we're looking for this capital T. 146 00:10:34,810 --> 00:10:40,110 So when we hit at 8:00 we can see this time it gave us return the number four. 147 00:10:40,160 --> 00:10:42,570 And that's because this is the fourth character in this string. 148 00:10:42,580 --> 00:10:47,770 And again we searched from the back to the front which is why we encountered this t instead of this 149 00:10:47,770 --> 00:10:51,340 beginning t that we did with our regular in string. 150 00:10:51,370 --> 00:10:54,640 Now the last thing we're going to test out is the replace function. 151 00:10:54,640 --> 00:11:01,210 So we've in the replace function have provided the string our original string that we're working with. 152 00:11:01,210 --> 00:11:03,040 This is the string that we're looking for. 153 00:11:03,100 --> 00:11:05,580 And this is what we want to replace that with. 154 00:11:05,590 --> 00:11:10,680 So in this case we should see the x y z replaced with the I N G here. 155 00:11:10,690 --> 00:11:14,470 So we'll hit evade and we can see that's what happened on our form. 156 00:11:14,470 --> 00:11:17,550 So had f eight one more time to end the sub. 157 00:11:17,590 --> 00:11:23,530 So in this video we covered the most common string manipulators that you're going to use in Visual Basic 158 00:11:23,560 --> 00:11:28,390 and you can pretty much use combinations of these functions to accomplish anything you need to accomplish 159 00:11:28,390 --> 00:11:29,830 within VBA. 160 00:11:29,830 --> 00:11:33,100 So now we can start to use more complex strings in our code.