William Rosener: hello, and welcome back in this video we learn more about string manipulation.
William Rosener: Before coming to an issue I worked at the University of Tennessee well if this was located in the administration button and it seemed like almost every day, I was helping someone.
William Rosener: format in data and this particular case may be, they had entered the Microsoft excel enter the data last name comma first them, and now they wish that it was broken up into two columns or vice versa them.
William Rosener: And you can make someone really, really happy really quickly in this case, there may be seven names.
William Rosener: And only take me a few minutes, but when it gets 70 names or 7000 names were 70,000 names you're going to find you're going to save them hours and hours of time not only save time, but most likely.
William Rosener: result enough much more accurate document there okay we're gonna go ahead and do this one twice over, and do it once use an A for loop summary screen press on that and also the second time, using a while loop term.
William Rosener: OK so again all we're going to do is we've been or data in this format.
William Rosener: And we're going to extract the first name and last name Okay, and the same area.
William Rosener: As you found this video, you should find the interface, which will save you a little time there and let's go ahead and start what I typically do is before getting to go into a loop.
William Rosener: i'm going to work on this first piece of data extract first name and last name once i've got that work and we'll put this all into a loop so i'm gonna go ahead and press on extract.
William Rosener: Names one and we can see that the first information is in cells, a two, which is row two column one so i'm going to Double Click on extract names.
William Rosener: me that over here in the window and maybe the first step i'm going to do is like declare some variables first name.
William Rosener: Last name engine when I, these are actually string so you can come along and type in string here.
William Rosener: And i'm going to put in a coma position.
William Rosener: And that's an integer.
William Rosener: yeah so you want to go ahead and done well, eventually i'm going to add a loop, at which point i'm gonna need to insert the variable there are them so take a look here, one more time here.
William Rosener: The state has got to be this case we've got the data format it's got to be formatted or if it's some type of randomness there we wouldn't got to do this again, I think I mentioned before, if there was.
William Rosener: like this there's no way we can extract this data there, but in this case we're going to have.
William Rosener: A comma and a space, so we got last name comma space, it could be a colon it could be a dash a slash anything as long as there is some consistent dilemma between the first name or last name and the first name there so let's come right back here to the code.
William Rosener: And in this case that we've got.
William Rosener: Last name and then the first name, and let me just go ahead and.
William Rosener: 0123 okay.
William Rosener: So in bb visual basic string started position one, so what we want to do is we're going to look for that common position in this case i'm going to find a deposition eight.
William Rosener: So i'm gonna take the first seven characters that's going to last name and i'm going to go common position, plus two and extract the rest of information there okay so we're going to go ahead and say first of all, we need to find them comma.
William Rosener: And we'll use the incident command a first parameter is where we're going to start at i'm going to start in position one, the second parameter is the string on to be searching through and that's going to be cells row two column one and again wrote, which will put this in the loop.
William Rosener: And then i'm going to go ahead and put a comma.
William Rosener: And then i'm going to do a case insensitive search, which is a one so after this line is executed on this particular name, it would have a value of a so haven't got that I can now extract the last name and the first name last name.
William Rosener: i'm just going to use some limited function, I could actually use the left as well, so soon as I type in mid I could see the very first parameter is this train when we extract from it's going to be cells.
William Rosener: To comma one.
William Rosener: Next parameter here, you can see a start as start as a certain position one, and then the next one is the length Okay, so if I found a position, the common position eight.
William Rosener: I can hard code this and put a seven there and it would great work great on my name, but I wanted to work on in in as it's going to be a comma position minus one okay so on the comment eight eight minus one is seven, then the first name.
William Rosener: You know, choose the MID function cells row two column one.
William Rosener: And now, what I want to do with summer started position 10 in this case it's going to be a comma.
William Rosener: position, plus two.
William Rosener: Okay, so i've extracted the last name the first name, we now need to write that data out and where, am I going to place that i'm going to place in row two column to i'm going to place the first name in a row two column three will place the last name.
William Rosener: check this out and it should work on the very first piece of data there, and once you put your name in there, so if I now run this.
William Rosener: Sure enough it's going to extract the first name and last name and number work, regardless of the name entered here.
William Rosener: Okay, so I can actually delete that and extract name, Sir Okay, so now we want to put this into a loop.
William Rosener: And i'm going to do it twice here that's good to these commandments the first time i'm just going to go to date, and again I could go to 280 or two 8000 whatever one will put it into a for loop.
William Rosener: Okay, so i'm going to come along and take about one i'm gonna go ahead and i'll go ahead and start my loop.
William Rosener: gap as soon as I do that I can see okay i've cracked using a variable called index someone can read up here and create a variable that i'm in debt this all over here.
William Rosener: I can actually even believe that.
William Rosener: Okay, and why two to eight.
William Rosener: We go from two to eight.
William Rosener: OK, so the very first time that goes through index is going to have a value of two so rather than the hard cabinet with this to i'm going to put an index here.
William Rosener: and actually i'm going to change all of these is here tues with selves So the first time through next as a value to someone go to one to one i'm gonna come here i'm gonna put an index, and the same thing right here.
William Rosener: index.
William Rosener: And index, so the first time through i'll be looking at road to extract information to the last name extract road to the first name writing information, etc, to row to then they go through and do the same thing with three there.
William Rosener: Okay So hopefully at this point.
William Rosener: Sure enough it's now going through grabbing all of the data there okay haven't done that, with the for loop the drawback to this approach is well what if I added another name down here.
William Rosener: Well, this current Code is not going to work so let's just go ahead and modify this code, a little bit here to make it continue to go until it reaches non empty sadhana to stop here.
William Rosener: So i'm gonna go ahead and Double Click on extract names to and just to save some time here i'm going to grab all this code right here i'm going to copy it.
William Rosener: and paste it.
William Rosener: look a little bit higher on the screen here.
William Rosener: Okay, so now, rather than maybe calling this index i'm going to call this broke counter.
William Rosener: Okay, and i'm gonna start my role counter.
William Rosener: at two and why do it's because the head real one is really my header information so i'm gonna be going on routine.
William Rosener: So I start my rope counter rope soon i'm not going to go well, not this empty.
William Rosener: cells.
William Rosener: Real calendar and one some of you looking at cells rutu column on the very first time, get rid of this.
William Rosener: And I need another privacy out here.
William Rosener: And now, rather than put in the next.
William Rosener: we're going to close the wall of them Okay, and actually now need to probably a couple of changes here, since I call that road counter i'm going to go ahead and have to call these real counter as well.
William Rosener: In one huge thing before we run it if you run as right now what's going to happen you're going to come here and say row counters to is.
William Rosener: row two column on not empty it's not it's going to come through next cute it's going to come back up.
William Rosener: Is row to column or not empty and you're going to get into an infinite loop so need to do is come down here and go row counter it was real counter plus one, and what I probably should have done at the top, is the very, very top up here just typed in option.
William Rosener: Explicit that way, I thought misspelled row counter i'm going to get a syntax error, rather than a runtime error there with an infinite loop, so I think i've got it here check this out here.
William Rosener: i'm just going to go ahead and run this see what's up.
William Rosener: Okay i'm gonna go ahead and delete this these names right here i'm going to press on extract names too and sure enough in this case i'm now i'm going to keep getting in this case i'm going on down.
William Rosener: And I have another name sure enough it's going to just keep on extracting data they're.
William Rosener: Often, this video you learned a little bit about string extraction this case use in Microsoft excel and My guess is, if you work in an administrative office, and if you know some of these skill sets you're gonna make some very if not yourself okay next video thanks.