WEBVTT 1 00:00:01.680 --> 00:00:11.340 William Rosener: Well, welcome back and this instructional video you'll learn about reformatting data using Microsoft excel nba visual basic for applications. 2 00:00:11.880 --> 00:00:18.210 William Rosener: For I got into teaching I was a webmaster at the University of Tennessee and my office was located in the administration building. 3 00:00:18.720 --> 00:00:28.440 William Rosener: So in addition to working on the website, it seemed almost on a daily basis, I was helping someone format or reformat data frequently using a spreadsheet like Microsoft excel. 4 00:00:29.250 --> 00:00:40.140 William Rosener: For this is a perfect example here imagine someone has entered in the address the single cell at the address at the city i've got the state and the zip and one so. 5 00:00:40.530 --> 00:00:52.980 William Rosener: it's will be doing this exercise is current a little command button here called extract addresses, which is going to extract the address city state and zip place them in their own columns which point I can probably come along and delete us. 6 00:00:54.270 --> 00:01:01.800 William Rosener: Well, now that we know what to do, let's get started, so in the same area as you find the link to this video, you should also find. 7 00:01:03.180 --> 00:01:18.120 William Rosener: A link to the data and you're going to notice that this ends with an m, this is a macro enabled workbook So if you want to say this is another upon make sure you save it so it ends with xml extension so xml has mm per macro enabled. 8 00:01:19.500 --> 00:01:26.640 William Rosener: I should probably also format mentioned that this data is carefully formatted here, so I put a have an address a comma. 9 00:01:27.210 --> 00:01:38.910 William Rosener: In the city comma state and zip and if this information was not formatted, for example, maybe, if I had typed in information like this. 10 00:01:39.510 --> 00:01:46.140 William Rosener: Well, I do have that capital Q here, I could possibly search for but otherwise this be impossible to a format, because I don't really have. 11 00:01:46.470 --> 00:01:54.090 William Rosener: Any separation there I could use semi colons I could you space as accepted but I need something that I need to search and find out. 12 00:01:54.540 --> 00:02:04.140 William Rosener: So I can go to the first comma that's going to be the address and information between the commas, is the city and the state and find the last five would be the zip. 13 00:02:05.400 --> 00:02:13.200 William Rosener: Well, the first thing we need to do is get our developer tab this long I should probably mention I don't think you were complete this on on MAC I don't think it has this. 14 00:02:13.770 --> 00:02:23.640 William Rosener: Recent versions to not have bba install maybe newer one soon so first up i'm gonna do is come up here to file i'm going to come on down here to options. 15 00:02:25.200 --> 00:02:37.950 William Rosener: And i'm going to choose customize ribbon need to come over here and choose the developer tab and make it visible so customize revenue, make sure your developer tab has turned on and which one i've now got the developer tab. 16 00:02:39.270 --> 00:02:41.670 William Rosener: So now, what I can do is I come along and I can. 17 00:02:42.780 --> 00:02:53.670 William Rosener: come right over here press on the developer tab insert right under active X control, I can press a lot of command button no come on down here and i'll drag out a command been. 18 00:02:54.840 --> 00:03:05.160 William Rosener: This is optional, but i'm gonna go ahead and right click on this, let me go to properties run the same command module on haven't say extract addresses. 19 00:03:07.350 --> 00:03:08.460 William Rosener: And i'll go ahead and close out. 20 00:03:09.540 --> 00:03:19.110 William Rosener: OK so again, the first step we're going to be doing is searching for the common what i'm going to do is i'm going to be working on just this first piece of information it's like. 21 00:03:19.590 --> 00:03:28.740 William Rosener: it's going to be in row two column one i'm going to grab the address you say once i've got that work and i'm going to put it in a loop and i'm going to go to to 11. 22 00:03:29.280 --> 00:03:36.690 William Rosener: But I could easily be going to to 10,000 so what you're going to find is, if you only had 10 London address is now you're probably would say much time. 23 00:03:37.140 --> 00:03:48.480 William Rosener: If I started having 10,000 of a sudden, I started doing the math i'm going holy cow, this is going to take me 10 hours and no one's going to take me 10 hours, the chance to me, introducing some air at least. 24 00:03:49.530 --> 00:03:57.120 William Rosener: it's going to be out the roof there's stuff i'm working on something for 10 hours repetitive task I guarantee i'll be inserting some mistakes them. 25 00:03:58.260 --> 00:04:08.040 William Rosener: So anyway let's go ahead and get started here so what's going to happen is methods design mode if I now pressed on it's going to execute because I don't have any code execute. 26 00:04:08.460 --> 00:04:14.850 William Rosener: That this is a turn on now, when I Double Click on the command button it goes to the source CAP so it's going to come right up here. 27 00:04:15.930 --> 00:04:20.460 William Rosener: So maybe the very first step i'm gonna do is come right up here to the top and i'm going to type in. 28 00:04:22.140 --> 00:04:25.320 William Rosener: You actually put it about their home it's iPad option explicit. 29 00:04:27.570 --> 00:04:30.840 William Rosener: And now, if I have any storms text it's going to let me now. 30 00:04:32.250 --> 00:04:37.620 William Rosener: So the first step i'm going to do well to you and i'm going to come right back here and let's grab this information. 31 00:04:41.100 --> 00:04:43.620 William Rosener: And you won't have to do this, but i'm going to come right it. 32 00:04:49.740 --> 00:04:59.550 William Rosener: Okay, there we go and i'm going to paste now what i'm going to do is come right down here and just start typing some notes i'm going to type numbers 12345. 33 00:05:01.020 --> 00:05:04.860 William Rosener: Just so I can it can help visualize what's going on. 34 00:05:09.660 --> 00:05:25.890 William Rosener: Okay, so what's going to happen is we're going to search for the first comment and everything to the left that it's going to be the address once we get we're gonna look for both comments once I found this comma, will be able to extract city and extract the state and, finally, the zoo. 35 00:05:27.300 --> 00:05:44.790 William Rosener: So the very first step, I want to do is I create a couple variables here first comma position and i'll Type one called second comma position and so that should that the aim is just going to declare variable i'm not actually going to go ahead. 36 00:05:45.930 --> 00:06:00.450 William Rosener: And extract fine and how you propose common position well to do that I wouldn't use a function called in string and the first parameter is going to be the starting location, the second parameter is going to be. 37 00:06:01.770 --> 00:06:06.780 William Rosener: Where Am I extracted from and i'm going to get that information from row two column one. 38 00:06:10.200 --> 00:06:14.160 William Rosener: i'm gonna be looking in cells row two column one. 39 00:06:15.180 --> 00:06:19.650 William Rosener: The third parameters what am I searching for and then it's just going to be a simply a comma. 40 00:06:20.490 --> 00:06:26.490 William Rosener: And then i'm going to go ahead and do a case insensitive services as soon as this thing is capital or lowercase comments. 41 00:06:27.270 --> 00:06:44.730 William Rosener: So I want us to search through starting position one, and I should find this in position nine so i'll tell you what let's go ahead and just check on the type of cells to two and i'll be actually delete the sermon its first comma position. 42 00:06:46.230 --> 00:07:00.330 William Rosener: So i'm going to go ahead and close this i'm going to now press on that design mode so now, when I press on my command bundle executes the code and Sure enough, I have found that comma in position nine. 43 00:07:01.290 --> 00:07:09.300 William Rosener: Some repress back on the mind design mode, we come back here to extract address and i'm actually going to delete that since I don't really need that. 44 00:07:10.920 --> 00:07:13.410 William Rosener: Okay next thing we're gonna do is we're going to get the second. 45 00:07:14.490 --> 00:07:15.060 William Rosener: Common. 46 00:07:16.140 --> 00:07:24.930 William Rosener: position and to do that one I don't want i'm going to start I don't want to start in position one otherwise i'm going to find this exact same comma. 47 00:07:25.440 --> 00:07:36.000 William Rosener: So rather than starting position, one I am going to start in this position, right after the first one, so that was going to be first comma position plus one. 48 00:07:37.440 --> 00:07:46.830 William Rosener: Now so i'm going to be starting everything else is going to be the same i'm not going to be starting position 10 stuff upon the person in position nine i'm not going to start in. 49 00:07:47.670 --> 00:07:56.670 William Rosener: nine plus one is 10 so we started position 10 i'm going to be looking through a cells to common one again looking for a common. 50 00:07:59.010 --> 00:08:11.880 William Rosener: And as extensive search if you wanted to you could go ahead and check it out place that information as well and, for example, cells to two to three or something just to confirm that now, when should be and what they want. 51 00:08:13.170 --> 00:08:15.120 William Rosener: So those two two. 52 00:08:16.140 --> 00:08:18.390 William Rosener: equals second. 53 00:08:20.190 --> 00:08:24.690 William Rosener: position so i'm looking at here, it should be in position 17. 54 00:08:27.120 --> 00:08:34.170 William Rosener: So if I now press the design mode now when I press on it shut up I am finding in position 17. 55 00:08:37.260 --> 00:08:38.730 William Rosener: Okay, I now know. 56 00:08:41.280 --> 00:08:53.910 William Rosener: Where the first comma occurs that or the second I can now begin to extract information so i'm gonna start off with the address in the first thing I need to do is to just go ahead and write variables, for all these. 57 00:09:08.640 --> 00:09:26.250 William Rosener: Okay, so now i'm going to go ahead and extract the address and so that's going to be this information, right here whoops right there okay in to extract the address i'm going to use the love friction i'm going to specify the strength or that's going to be at itself to one again. 58 00:09:27.270 --> 00:09:35.790 William Rosener: and the next one is the leg, and if I wanted to I could hard code this and put an ad here but it's only going to work for this address because that's going to extract. 59 00:09:36.390 --> 00:09:47.460 William Rosener: Left eight characters what I wanted to do is to work on any address so i'm going to put the worst comma position minus one. 60 00:09:49.230 --> 00:10:05.580 William Rosener: So I found the first comma position at nine so this year command right up here it's going to have found the first common position, so in this address it was that position on an extract nine minus one is eight i'm an extract left eight characters and. 61 00:10:06.630 --> 00:10:13.680 William Rosener: place that in address now I can actually go ahead and type in cells to comma two. 62 00:10:15.660 --> 00:10:22.710 William Rosener: equals address, and now we can go ahead and check that so i'm going to close this out come back here. 63 00:10:23.790 --> 00:10:35.490 William Rosener: i'll go my design modes and now, when I press it executes and sure enough i'm getting 27th avenue as the address okay i'm going to continue on will get the city next. 64 00:10:37.140 --> 00:10:45.000 William Rosener: Again, and to get the city, the very first step, I need to know is how many characters is the city so i'm going to type of city length. 65 00:10:47.340 --> 00:10:48.180 William Rosener: is equal to. 66 00:10:50.640 --> 00:10:56.940 William Rosener: Second comma position minus first comma position. 67 00:10:59.820 --> 00:11:01.470 William Rosener: minus two. 68 00:11:02.520 --> 00:11:14.400 William Rosener: So let's just play this some numbers in here i'm going to find the second one at position 17 make a podium or delete this herb and i'm going to say i'm 17. 69 00:11:16.500 --> 00:11:19.110 William Rosener: minus nine. 70 00:11:21.810 --> 00:11:47.190 William Rosener: minus 270 and Madison nine is eight eight minus two is six so 123456 so in this case it's going to extract Queens well actually it's going to find the length of the city to be six characters long so now, I can go ahead and extracts. 71 00:11:48.420 --> 00:11:58.560 William Rosener: And it's going to be, starting with the MID again this extract the left, and so the first parameter is destroying i'm extracting from and that's going to be cells to comma one. 72 00:11:59.970 --> 00:12:09.780 William Rosener: The next parameter is where, am I going to start at I am going to start, I found the common position, nine and want to not just work on this one, I could put 11 here. 73 00:12:11.580 --> 00:12:19.410 William Rosener: But i'm going to go first comma position let's do so, I found my first comment position in nine. 74 00:12:20.460 --> 00:12:32.490 William Rosener: i'm now going to add to that that's going to be 11 and now how many characters like an extract I could hard code this with six in this case, I wanted to work on anyone so i'm going to put in. 75 00:12:34.110 --> 00:12:35.520 William Rosener: city length. 76 00:12:38.160 --> 00:12:41.370 William Rosener: Okay, now we can come down here and i'm going to place that. 77 00:12:42.480 --> 00:12:43.620 William Rosener: In two three. 78 00:12:47.100 --> 00:12:48.570 William Rosener: is equal to city. 79 00:12:50.340 --> 00:13:03.180 William Rosener: Okay let's check this out, I can now toggle this is on extract address and Sure enough, I am extract in the city okay now we're going to go ahead and extract the state upon like the zip. 80 00:13:04.320 --> 00:13:06.210 William Rosener: Okay, to get the state. 81 00:13:07.830 --> 00:13:09.600 William Rosener: We can only use of the mid function. 82 00:13:11.400 --> 00:13:13.140 William Rosener: And the first parameter is going to be. 83 00:13:14.220 --> 00:13:15.720 William Rosener: Sales i'm extracting from. 84 00:13:17.130 --> 00:13:18.570 William Rosener: The House to one. 85 00:13:19.860 --> 00:13:30.360 William Rosener: The next one is going to be the position i'm starting out so in this case I could put a 19, I just wanted to get on this one, I could put 19 into. 86 00:13:31.170 --> 00:13:40.950 William Rosener: And it would start a position like Tina and extract two characters, but I wanted to work on any on any address so i'm gonna go ahead and put on second comma position us to. 87 00:13:42.330 --> 00:13:51.150 William Rosener: into so second comic position was 17 i'm going to add to that it's going to be 19 normal extract to character so. 88 00:13:52.560 --> 00:14:03.180 William Rosener: So now, I can come down here, and I can say cells to comma four is equal to the state now let's check it out. 89 00:14:04.950 --> 00:14:10.050 William Rosener: And back here toggle stock addresses and sure enough it's extracted the state. 90 00:14:12.810 --> 00:14:15.000 William Rosener: Okay, finally, the last one is the zip. 91 00:14:17.610 --> 00:14:19.140 William Rosener: In here we can just take the right. 92 00:14:20.490 --> 00:14:22.410 William Rosener: cells to come along. 93 00:14:24.120 --> 00:14:28.020 William Rosener: Like the right five characters there and I realized sometimes addresses. 94 00:14:29.280 --> 00:14:35.610 William Rosener: In this case, the zips are five sometimes as opposed to like nine but with these the map handle that later on. 95 00:14:36.960 --> 00:14:38.760 William Rosener: Okay, so take a look here. 96 00:14:40.050 --> 00:14:45.000 William Rosener: Let me just make sure I have that all ready to go so i've got the zip know when it come down here. 97 00:14:46.680 --> 00:14:53.280 William Rosener: and sells two comma five is equal to the zip code. 98 00:15:00.720 --> 00:15:08.940 William Rosener: Okay, and that is working perfectly so at this point we've got that first report on the first address extracting the. 99 00:15:09.600 --> 00:15:29.700 William Rosener: Address city state zip now where to place this in a loop and i'm just going to go to do loving again I could go to to 20,000 and much more impressive them okay so i'm gonna come back here to extract addresses I need to declare one more variable here index as an integer. 100 00:15:36.570 --> 00:15:36.960 William Rosener: oops. 101 00:15:38.310 --> 00:15:45.090 William Rosener: The aim index as to the gym okay now i'm going to go for an index equal to 11. 102 00:15:46.800 --> 00:15:53.850 William Rosener: And i'm going to come down to the bottom and i'm going to type next, and it really helps you find most people are going to embed these. 103 00:15:59.100 --> 00:16:00.510 William Rosener: About even delete that space. 104 00:16:04.260 --> 00:16:16.260 William Rosener: So we're going to put this into a loop and it's going to go to to love it, so the first time through index has devalued, two and three and four in the pod except for that and again just to make sure you know i'd love to go to to 11. 105 00:16:17.340 --> 00:16:35.550 William Rosener: Now I need to do is rather than looking at selves to one i'm going to change that index, so the first time through I look at two one, but the next time i'll again 3141 and so forth, so where i've got these cells to one simple change that index. 106 00:16:58.110 --> 00:16:59.310 William Rosener: yeah one warm. 107 00:17:01.230 --> 00:17:01.620 William Rosener: Up I got. 108 00:17:05.130 --> 00:17:11.310 William Rosener: left behind so let's go ahead and give this a whirl and check it out and i'm going to come back here to find one more than. 109 00:17:12.570 --> 00:17:22.050 William Rosener: So i'm going to press on toggle this so now, when I press on it executes the code and Sure enough, it looks like everything is working like a charm. 110 00:17:23.460 --> 00:17:31.050 William Rosener: Okay, so in that case, what did I do is we are now searching, so you can watch that one more time here on. 111 00:17:32.100 --> 00:17:41.610 William Rosener: track addresses and there we go so probably a couple things to keep in mind here is make sure if you receive this and some other format it ends with that him. 112 00:17:42.000 --> 00:17:49.020 William Rosener: Macro enabled work, but if I just say save this as a regular Microsoft excel spreadsheet all this time is spent. 113 00:17:49.860 --> 00:18:03.780 William Rosener: Creating this this information is not going to be stored so anyway, hopefully in this video you'll learn how to begin to do a little reformatting and Microsoft excel using bba and i'll catch the next video thanks.