Create your own web scraper! - VBA - No Coding Experience Needed - 2021 | Philip Koehler | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Create your own web scraper! - VBA - No Coding Experience Needed - 2021

teacher avatar Philip Koehler

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

9 Lessons (49m)
    • 1. Intro

      0:30
    • 2. Set Up VBA

      3:06
    • 3. Your First Web Crawler!

      14:15
    • 4. Sharing Your Macro

      1:19
    • 5. Advanced Web Crawler

      8:26
    • 6. Expert Web Crawler Set Up

      14:01
    • 7. Expert Web Crawler Looping

      4:11
    • 8. Cleaning the Data

      1:52
    • 9. Last Words

      1:20
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

62

Students

--

Projects

About This Class

Do you want to learn how to quickly program your own web crawlers ... all in Excel? Then this class is the right one for you. No coding experience needed. We'll create 3 web crawlers that you can use for your own projects or work.

I am looking forward working with you!

Meet Your Teacher

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. Intro: Welcome to my VBA class. My name is Philip, and together we'll build web crawlers that go onto a website, put in information and then download the results. But don't worry, you don't need any VBA experience and you don't even need Excel experience. All you need is Excel. As long as you have Excel installed, we set it up together and create those webcasts. I'm looking forward to build those webcasts with you, and I'll see you in the next video. 2. Set Up VBA: Hi, I'm Philip. I'm your tutor for this class. And we will build three web crawlers and total. But before we do that, we have to set up Excel. And for VBA, it's pretty straightforward. Everything you need is embedded in Excel. So as long as you have Excel installed, you're pretty much good to go. And as you see, I have one sheet open, call it the VBA web scraper cheat sheet. And this sheet isn't a course resources. So you can download the sheet and open it. And after you've done that, let's go ahead and enable VBA. We can do that by clicking on File on the top left. Then on options. In the bottom left. Customize Ribbon. And here you see we have almost everything enabled, but two things. And one of them is developer. So let's go ahead and check mark developer. And after we did that, we can press. Okay. And we see no up on top, we have a new tab called Developer. And as we can see, we have a couple of new features. We don't need most of them. We actually only need three. And the most important one of those as Visual Basic, which stands for VBA. So let's go ahead and click on that. And this will open the Visual Basic window. This seems to be a new application, but all of this is embedded in Excel. Everyone that has Excel has access to this and this makes it very powerful. And that's why VBA so commonly used. And as we see here, this is the cheat sheet. So this has pretty much all of the important information that you need for this course, but also for your future right here. And before we can get started developing web scrapers, we need to enable the right libraries, that just one additional library. And we can enable that by clicking on towards selecting references. And then we have to scroll down until we get to M, Microsoft and Microsoft Internet controls. So let's see where it is. And it's right here, Microsoft Internet controls. Let's check Margaret and click, Okay. And now you're all set for getting started to build your first web crawler. And if you don't have this immediate were no open, That's just do then you can click on View Immediate Window. And then this one should open up. 3. Your First Web Crawler!: Know that VBA is set up. We can start and program of our very first web crawler. And what we wanna do is an open Internet Explorer. Go to the search engine of choice, in this case, DuckDuckGo.com. Then after it opens, we want to put in a search term. Let's say we want to open NPR. And then we want to click on Search, weight again until everything loads, and then click on the actual and PR website. Here we go. So let's do that. Exit out here, and let's get into VBA. So we'll click on Developer Visual Basic. And know VB is open. And let's create a new module. This is our cheat sheet. So this file, should, you already should have downloaded that if you did not do that yet, please download it and pause the video and open VBA. And we want to leave this macro as it is, because that's our basics. We copy and paste from here into our new module. So let's just keep this as it is. To create a new module, we click on Module 1, right-click. Insert module. And we'll get a new, fresh and clean module. And degraded VBA works is that you always have to start off with writings up. Then you name your macro, I'll name mine rep, crawler one. And then you have an open and closing bracket at the end and press Enter. And here we see that VBA automatically created, adds up all of your code has to be in-between those two lines of code. If it's outside, then it won't run. So make sure it's inside of it. And the first thing we wanna do is open Internet Explorer. Let's go back to our teach it. And here we see we have Internet Explorer. And let's copy the first three lines. Copy and paste it in between. Okay? And what this actually does is it defines IE, IE object as an Internet Explorer. And then the next line creates a new Internet Explorer window. And all of that happens in the background. And to make it visible to us, we have to use the third line. And that makes the code editor dental explorer visible. And a really powerful thing with VBA is you can execute the code line by line. This makes it really helpful for us to understand what the code is actually doing. And it also makes it really easy to troubleshoot compared to other programming languages. And you can execute code line by line by pressing F8. So go ahead and press F8. And you see this little yellow arrow here and yellow highlighted text here. That means that currently we BAs and this line. So if you press it a couple times, you see that a new Internet Explorer has been created. So the next thing we wanna do is tell in the legs bar or where to go. So let's exit out here. And you see that the code is still an execution mode. So let's just click up on here, reset. So that closes this out. And let's go back to our cheat sheet. And we see the next part is navigate to URL. So let's copy that. Control C. Copy and paste it in here. And you might have guessed, you have to put in the URL in-between. So let's write Duck, Duck, Go.com. And a good practice is to always test your code if it works properly. So we will be writing code tested and then go to the next section. And to do that, we can go through it line by line, or we can press play or a five to execute everything at once. So let's do that. And we see it successfully opens DuckDuckGo.com. Great. So the next step is to put in awhile NPR search term right into the search box here. And the way this works is that we have to find this part and the HTML code. Because we have to tell VBA that we want to put it in a certain part of the website so we don't want to put it anywhere else. We want to put NPR, right, exactly. And here. So that's what we have to tell VBA to do. And all browsers have this option. If you click, right-click, you have Inspect Element. So right-click Inspect Element. And this will open and very useful. You see it's already blue highlighted. What do you need to look into? So all of this around it. Don't worry about it. As of now. You just need to focus on the blue highlighted text. And the way that HTML box that it's categorized by, for example, this class term. And that is what we are being or that's what we're looking for. So you can see all of the red terms here. There's something else. And really we only care about class. So this, this is where we tell VBA to go into, to look for and then to put NPR. And so let's minimize this. Go back to our cheat sheet. And we don't want to click Read, I want to get text. We want to write into browser. So we can copy that. Go back to our module and paste it down below. And As you can guess, it has to go right in between here. So let's go back to our Internet Explorer final class again. Double-click copy. Go back to our code, and then just paste it in here. And now you see the value is already set to NPR. So you don't even have to change that. Know, this value should be written in there. It gets a bit overwhelming as of no, don't worry about it. As long as you can follow along. You're doing everything right. And you don't have to understand all parts of the code. Eventually the more often you do this, then more and more things make sense to you. You'll understand one more. But for the beginning, for the first part of this course, you really only have to follow along. And if you get the results that you want, then everything works great. So let's test our code again before we continue. We can do that by clicking on Run or selecting a five. I'll click F5. And we see a does not work. And this is a good, reasonable, good example why you always want to go back and make sure that everything works properly you, before you continue, because then it's a bit more difficult to find the error. So what we can do is closer than Internet Explorer and then we see that VBA actually gives us a message, but it doesn't really tell us what. But a cool feature is that we can click debug and debug tells us where the error was. So it tells us that this line was could not have been, couldn't be executed. So what we wanna do is go back to module 1, and we see that we actually didn't copy all of it. We have this application rate. So let's copy it. Go back to our module and paste it under Dr. go. The reason for this, why you can't put us in right away is that all websites take a while to load. And VBA is really, really fast and executing the code. So even if the website only takes two seconds to load, VB is already in this next step. And once you put in the NPR, but since the website and load yet, the VBA does not find that field and therefore gives you the error. So with this line here, you tell VBA just wait a couple of seconds before it continuous. So let's do that. Currently. It is set to one minute and 30 seconds. I don't think Dr. cortex or the lung. So let's just put in 05, which means five seconds. And let's try again. So we can exit out again and run it again. So it opens DuckDuckGo and after five seconds, yep, it writes and NPR. Ergo. So no, we already have the basics. And let's go a step further and actually look for the term NPR and hit Search. So what we wanna do is hover over the class that we want to find. So you want to hover over the magnifying glass. Right-click Inspect Element. And then you see this is highlighted. This does not make much sense and this happens sometimes. It gives you the wrong class. So it should say something in the class like search, because this is a search button. So what we can do is actually use this, select any element button here. And then you can select an element from the breadths or website. So it highlights where you're hovering over. So if you want to get this, you see this is highlighted know, and if you click on it, it gives us the right field in the http HTML code. And now we see it's blue highlighted again. And we have to look for class. And he has class. So let's not click on it because before we want to click on it, you want to set up our code so we can paste it into the right thing. So let's go back to our GG and find click. Okay, so here's clicked and we want to get elements by class name. So let's copy that. Paste it. And know right in between here, we can paste our class. Here it is. So let's copy that. Go back and paste it in between. And now it should click on go. Let's try it out. And let's hit Play or a five. Morale recall. So we see we already have our first search result. So now we see NPR here. So every, everything works as expected. And for the last step of our web crawler, we actually want to click on the link here. So as before, we right-click on it. Then we go to Inspect Element. And now we see the classes result a. And it already shows us the link as well. Npr.org. Let's go up here. And we want to click again. So we actually just copy this what we had before. Control C, Control V to paste, go back to Internet Explorer, copy the class. And no, we just paste it into here. And you may have noticed already, this is a new website that it's being loaded. So as before, we have to go in here until VBA, don't rush. Take five seconds and let the website load. So note that we did that. We can hit Go or run. And we see after five seconds should put an NPR, click go. Wait another five seconds and click on NPR. And very cool. So this is the first web crawler that we build together. Works, works nicely. And in our next web crawler, we actually want to extract information from the website. 4. Sharing Your Macro: Congrats, you created your first web crawler. And not only do we want to use it, maybe we even want to share it with colleagues. And if they don't have VBA would be a hassle to go into VBA, you know, enable it for them. So let's not do that. And Excel actually has a pretty cool alternative. So what we can do is go to our sheet. Then we go into the Developer tab. Then we click on Insert and choose the very top left button. And we can create a size that we want. So let's do a medium-size here. And now we can link our macro to this button. So we'll link the web crawler 1, 2 button. And here it is. And I'll just give it a nicer name, WebCrawler one. And now it is ready. So when we click it, it automatically executes the code. So this is a really cool way of sharing your VBA Macros and also for yourself, a few have shorter code, so it's cool to place them on your spreadsheet and you can easily access your VBA code. 5. Advanced Web Crawler: In this class, real program erupt kroner, that goes to a website and extract information from it. This is very powerful and very versatile. You can extract all kinds of information naturally from the web. And you can do it even several times a minute. So you can do it once a week, several times a day, several times a minute, really up to you. So there's wide range of projects you can use this red color for. And as we learned in the last video, you can create a button and really make it super easy for yourself and your colleagues. Before we get started, let's delete that button, control and left-click and delete. And let's check out the website we are, where we want to get our information from. Let's just open Internet Explorer and type in pie 21.gov website, Pi robot dot website. And let's hit go. And here we have a simple website that I created. So I chose that because I can make sure that nothing changes in the code, the HTML code. And let's scroll down. And I want to extract this, this part of it. So as before, what we can do is we can right-click and inspect element. And here we already see our HTML code. Very nice. So what we wanna do is open powerbi dot website and extract information. Great. So let's go back to the VBA code. And here's our script from the last web crawler, from the first rep Chrome. So let's keep that and create a new module. So insert module. Let's go back to module 2, Copy All, and paste that. So web crawling and programming in general, it's a lot about saving time. You don't have to do all of the step, steps over and over. It's much easier if you already have a project that is somewhat similar, that you just copy that project and then you can just change the way that you like it. So what we'll do as we go into EI object navigate URL and put our website. And so Pylab dot dot website. And then we'll just check. So it waits five seconds as he saw it loaded little bit slower. So let's do 10 seconds. And then it says, put in the value, we don't need that, We don't need to put in any value. You just need to download some data so we can delete that part. Then we also don't want, yeah, we don't need any clicks. So that is also that can be deleted. And now let's just create a little bit up. Okay, great. So now we go to our GG and check for getText. So we see here getElementsByClassName in our text. Gettext are great. So let's copy that line. Copy it, and go to our module and paste it. And before we continue, let's name it. Webcrawler two. Okay. So the next step, as you did before, Let's go back to our website and find that class that will paste right in the end. So we want this part of it. So what we have to do is hover over where we want to get the class name from. So in this case it's text. Right-click Inspect Element. And here we see this should be the right text. Everyone has a boring, repetitive tasks. Everyone has boring repetitive tasks. And he has asked the same. And now here comes to somewhat tricky part again. We have to find a class name that is unique. So we see here text editor, clear fix, that doesn't seem to be a unique class name. Usually it has some kind of numbers or digits that appear random, but can be seen as a code. If we go up widget container. Now that's not unique either. But we see here we have already two lines, and it also says data IDs, so that is a unique identifier. And we also see that the class name has that same unique ID. This, this pod as unique and whole website. So we'll use that so we can double-click on that Control C. Go back to our code and paste it in between the brackets. All right, Cool. Let's see how it looks like. But before we continue and run our code, right now, we just tell the VA to take the data, but we don't tell VBA what to do with it. So we can assign it to a variable. So let's go up and front and write text equals. And now this whole long part will be assigned to this variable. So the information that we find here is going to be stored and our new variable called text. And let's execute step-by-step. So you'll press F8. Node waits 10 seconds. And as we see, the website is somewhat slow to load. So it just made it private loaded and nine seconds. And let's hit F8 again. And let's hover over text. That is a cool feature two. And as you can see, it gives you a preview. So it tells you ready. Everyone has a boring, repetitive, has borne repetitive tasks. We got actually the right thing. And now we want to bring it into Excel. So what we can do as we'll go back to our cheat sheet. And if you scroll over, don't get taxed text and print to excel. And as you, as you see here, we have two lines of code. We used. The first one, and here's a second one. So this is somewhat a shortcut, but let's copy it and go to Module 3 and paste it below. We can just stop the script. And as you see, this tells you that the value of cells two comma two is suppose to be this. And we could do things. We could paste this whole, this whole long line, copy and pasted right behind you. Or we just use the new variable that we created called Text and paste it into here. And now this cell will get that value. And the way that Excel organizes the data is that You start from the left to the right. So two and then 12. So it's not ABC, it's 1234 and so on. So 2, 2 would be right here. So left to right. Let's go back into VBA. And if you know, execute with a five. We should get a nice result. We should see the, the text and our Excel. All right, let's go back and check. And urea, very cool congrats. You just created your first really useful web problem. 6. Expert Web Crawler Set Up: Welcome to the last and most exciting part of this tutorial. Now, we will create the third WebCrawler, Arab quarter that goes to a website, puts an information and extracts the results. And we're going to look at a real-world business scenario. So let's say a colleague of ours gave us a list, but 500 NAICS codes. Next codes are industry codes, which referred to, so each business has its own NAICS code. So your barber shop has a different code and let's say you're shopping mall. And our colleague gave us 500 of those next codes and we're supposed to find the definitions of those. But to test our code, we'll just use the first three. And those are 511 to 365 to five. Make sure it's in the B column. Then we're just going to name it nicks. And we want to put an a C column. You want to put our results. And before we get started with coding, let's look at the website to make sure we actually have to do So. Census.gov, forward slash nicks. And here we see we have three text boxes. We care about the first wonder, newest next code. And let's test that with the first one, 511, hit Go. And we have our search results right here. And we care about the first result, that 511, the three digit code, we don't get too much right now about the more detailed ones. We just want to find the publishing industry. So that's, that should be our result and that should be pasted into C2. Okay, let's go into VBA. Create a new modules. Right-click, Insert Module, module to copy everything. Paste it into module four. And let's name it WebCrawler three. And now we can already start off with just changing the URL to census.gov. Forward slash nicks. The five seconds is fine. The website loads pretty quickly. So that works for me. And then we see we want to put in a text. So we want to put in two things or two things, we have to change first the class. So the class here we can delete. And we want to change the value. And what we want to put in as 511. However, if you put it in like this, it means it's hard-coded. So it won't change. It's always going to be 511. But what we wanna do is that the code actually alternates through those NAICS codes. So it should start off with 511 and then go to 2, 3, 6, and 5 to 5. So what we want is a flexible code. So that means we create a new variable for it. So let's call it nicks. Make some space above. And let's define what makes stands for. Because VBA as of now has no clue what next is or what we mean with it. So we have to give it a value. And we can do that by writing cells. Open a bracket. And then you see it already gives us here a little hint. So it says row index and column index. So let's just put in two comma two. I'll explain that in a moment. So if you go to a spreadsheet, see, it always goes from the left to the right. So two and an AB. So 12 is right here. So there's two comma two, and that is 511. And as you see, this is actually hardcoded to. But we're going to use this as of now to just test our code. And in the second part of this video, we'll, we'll go into detail and make that loop. It's called a loop. Red alternates through the whole thing, but we'll worry about that later. So now this should be 511. And what we need to do now is find the class name. So let's go back to our website. Hover over the object. You will want to find our class name from 2017, like search, right-click and inspect element. And here we have class. Next search and put form control. And now you can imagine that this is actually not, doesn't sound unique at all. And we can test that by clicking on select element and say that probably those textboxes have the same class. So we see, yep, this has the same className and the 20071 has the same class name as well. So let's go back to our first one and just copy that anyway, because we know this is the first one. So if you go back and paste it in between the brackets, we can see that little 0 here. And that is important and very useful. And encoding the way it works. If you consonants, cone, count something encoding you don't count with 123, you always start with 0. So 0123. And that means that this 0 here refers to the first text box on the website. So this year or first, or this textbox, if you put in one, refers to this textbook, and then two to just text box. And that is pretty cool because if we were to use different text boxes, we can just change that one number, one or two, or 0. And then we can refer those to those different text boxes. But since we only want the first one, we'll put in 0 as it is right now by default. And a value now is next. Great. Let's test out the code. So we'll delete the next two lines and just hit F5. Perfect, So now we have 511 here. The next step is to click go. So let's hover over go. Inspect element. And here we have Nick search, submit, btn, which stands for button. So that is great. And as before, I would assume that this class is the same over here and here. So we also have to put in a 0 at the end. So what we wanna do now is click. So let's go back to our GG. Look for click. Okay, get elements by class. That's what we want to copy that. Go back to our module for paste it down below. It is already 0. So that's great. And let's go back to our web crawler. And let's look for this class. So next search, Submit button, copy that, and paste it in here. And now it should open the new side. So let's test that step as well. So I will close those Internet Explorer so it does not get confusing. And make sure your cursor is set in-between and collect five. So this happens sometimes. So it actually did not update you the, the textbooks for the text, but it's still found the results. So what you can do is that after you put in the next or in our case, the 511, you can give it another way time. And SEC even though it worked, right? No, We saw that it's not perfect and we wanted to just walk around or you make it work in a right way. Let's put in the timer and the next time you should be able to see the 511. Because as in the previous video mentioned, the VBA, VBA code executes really fast. So it puts in a 511 and right away clicks on go. So just tell the VBA code to slowly lower down with this. And then after clicked on go, we have our results here. So what we want is this publishing industry. So let's have over that right-click and inspect element. And then we'll look for the class again. So total class details. And we see known. Here's something you have to be careful about. You see the class is actually 511. So the data code refers to what we put in. So if we were to use this class, it would not be any good because with each code we put in. Changes. So let's just check and hover over this. And now we can see that if we hover over those different lines here, it highlights, highlights, different stuff. And have it. If we hover over our next list, we see it's still highlights the solid line. So this is what we know, what we don't want this very specific class name because it wouldn't not work. We want the next list. And if you scroll up and down, we see all of the results are actually called Next list, which is in our case actually pretty useful because as before, This is number 0, this is 1234 and so on. So we can read the same line of code, refer to different things. So if you just change that one number, you can refer to all of those different ones. But since we care about the first one, we'll just leave it at 0 when we program it. So let's go back. Make some space down below, go to IgG. And now we find that we want to print it to Excel. So here we have it. Let's copy the whole thing. Go back to our module form, pasted. And now we have to update two things. The first one is the value to sell, sorry, the cells. So now we see this is the row and the column, and we want it to be in the same row. So it's still should be in a row number two, but we wanted to be in column number three. So we leave the first two hazardous, but we go then to the second two and just put in a three. Okay, now our result will be pasted insults two comma three, and that's actually one cell. So in cell two comma three. And what we need to do now is update the class name. So let's go back to our Internet Explorer. And we already had Nick's less highlighted. So let's copy that Next underscore list and paste it into the class name. However, you may have spot, spot or that already. Since we had to go one level up. And it actually includes more stuff. So we see we have quite a bit of stuff and you can check easily what is included by clicking this era. So all of that stuff now is included. We don't include Edge HTML code, we only include detects only the black stuff here. However, since we're referring now to the upper level, the next list, also the text on Here's included. That is not an issue, but we just have to be aware that more texts than we expected will be included. So let's go back. And before we executed, you may have noticed one thing we have to do is give it a little bit time again. Because after we click on Search on the Submit button, we have to let a website load for, let's say, three seconds before we go to the next step. So let's try it out and let's hit F5. All right, it looks pretty good. It looks like it's doing what it's supposed to. Let's check an Excel and perfect, so a paste something into C2. 7. Expert Web Crawler Looping: Welcome to the second part. So in this part, we'll alternate through the NAICS codes. So we don't want to look up only the first chord. We want all three codes and information for it. So let's go back into Visual Basic. And what we can do is create a for loop. A for loop is a loop that goes through a certain set of code for a certain number. So what we can do is go right before our next definition. Make some space, and then go into our cheat sheet. If we scroll all the way to the bottom, we'll find the for-loops. And we'll copy that. Go back to our module form. Paste it in here. And now it says For x equals 1 to 10. So that means it goes through the code 10 times. We only have three. So let's put a number three. And as you see here, the next part is your code. And then next. So our code has to be right in here. So let's delete next. And to seal. And now let's place next at the end of our code, but before and sub C. So now what happens is that for x, X equals 1, 2, 3, this happens. So three times the code will go through here, go to next, go all the way to the beginning to four, to it again and again, and then go through it one last time. And as you may have noticed, this number, the cells is hard-coded. So right now, next, we'll always be 5, 11 because citizens, so two comma two. What we have to do is create a variable that is flexible. So just as we did right here with an x, we need to create another one that is flexible as well. So let's go before the for loop and create a variable called counter. You can name it any way you want to. But I like Honda. And let's put in two because that's our current value here. And let's replay, replace that value, that counter. So that means we're no, VBA wants to execute this. This is translated into two, so two comma two. And let's go further. And we see here's another cells. So let's replace that as well with counter and then goes to next. However, nowhere when it goes to next, It's still two. So what we have to do is that before it goes to next, it updates the variable counter. So we can do that by running counter equals counter plus one. So when it gets here, it is, It is two. So two plus one. Now it becomes three and it goes all the way to the four organ, goes through the whole code. And then it is three plus one. So the new counters for it goes through it again. And ask performed. It is kind of nice to just let BBA wait a minute, sorry, another minute, a second. And then before it continuously with the loop. And this was already it. So we just added a couple more lines. And if you now click on Run, we should be able to loop through all of the cells. So not just 511, but then also to 36. And then at the end, five to five. So let's look at our Excel spreadsheet. And very cool. 8. Cleaning the Data: So we got all of our results. And however, we're not quite done yet because we want to clean up the results. So one good practice is to always use the formula clean. So equals clean. And then C2. And then we can drag the formula down. And the next step is that even though after the column there's a lot of important information seems kind of interesting. We don't really need that, we just need the beginning. So everything up to the column. And one cool way is using the MIT formula. So what you're going to write as equals MID, open a bracket. And then you have to decide which, which texts you want to use. So D2, the start number is one and the number of characters. So what the formula is asking you here? How much should it take from the formula? So if you take 10, it takes the first 10 characters. And we don't want that. And actually also it's not always the same characters here, the column somewhere else. And he has a little bit further along. So what we wanna do as 31 and make it flexible, and actually want to include everything up to the column. And we can do that by including the find function. So what we want to find a steel column and then we want to find it in D2. And now we can close the brackets two times. If we had. And to know and drag the formula down. Now we see that everything is included, included up to the columns. 9. Last Words: Congratulations, you made it through the end of the course. I'm really happy that you follow through. And now you have a couple of RAB cross and a cheat sheet to use in the real-world. But before you go, I want to give you two things along the way. The first one is, errors happen. Even if you get really good at VBA and even other programming languages, hours always happen. So don't worry about it. Don't get discouraged. Sometimes you get stuck and it takes a while to figure it out. But that happens through all programmers. And second tip is use Google or DuckDuckGo in our case here. And try to find the answer. All programmers use it. You don't start off writing the code and then it's perfect as he saw, you go through it step-by-step. You tested as you read it, you write a couple lines tested, right? Couple more lines test again. So just try to find the answer. And someone I will show you someone probably had the same question already and someone answered it for them. So try with Google. And thanks for taking the course. I'm happy that you did, and I wish you really happy VBA programming experience.