William Rosener: Hello welcome back in this video you'll learn how to perform string manipulation in Google sheets using javascript.
William Rosener: let's start by taking a look at what you learn how to create and then we'll walk through the steps, though.
William Rosener: So there were step here is we're going to create a command button called test were some very basic stream manipulation is similar we've got a string a B, C D E, F.
William Rosener: will find the first two characters the middle two characters the last two characters the last character and the position of where that see occurs in this position this.
William Rosener: Training stringer somebody hit the test and sure enough it's going to extract a be the middle two characters the APP for the last two the APP and what position does the seeker at.
William Rosener: that's to once we've got that down kind of walk through some basic streaming replication we'll go ahead and work on for catalyst over here city.
William Rosener: State and zips and assuming someone in there, and this information, then realize now I wish my broken them up into three columns will be walking through that.
William Rosener: Okay now let you know what we want to do and let's get started here.
William Rosener: So the very first step, you can do is to hopefully have already got your account on Google and you're going to press on a little dots up here and you're going to go to the drive.
William Rosener: Okay next step is i'm going to press on new on drive and i'm going to choose Google sheets and will be creating this all from scratch room.
William Rosener: So, probably save a little time low on PR PR stuff I should mention is the same areas you found a link to this video, you should also find file called addresses it's Microsoft excel file so i'm just going to hit control a.
William Rosener: Control see the copy that we come right back over here and just hit paste.
William Rosener: Okay let's do just a little bit of formatting here, I see all these lines here, maybe i'm just going to come right up here select a control a to select all and I am going to remove all the borders.
William Rosener: And i'll highlight just the top line.
William Rosener: we'll put a bottom border on that and then maybe I can highlight just columns the first column and insert a right border.
William Rosener: Okay, and i'll probably have to increase the size of the city, but the state just going to characters in the zip code probably be decreased there.
William Rosener: OK now let's go ahead and create a couple command buttons to do that i'm gonna come right up here to insert i'm gonna go to draw in.
William Rosener: i'm going to drag out a text box and first one is going to be called test.
William Rosener: And then we'll put javascript here.
William Rosener: And maybe i'll get a color as a little phil color and then, if you want that let's go ahead and I get that text centering in there and i'll press save and close.
William Rosener: It and save and close okay there we go.
William Rosener: Okay next let's go ahead and create one more button over right here i'm gonna come right back up here insert drawing.
William Rosener: I got our text box.
William Rosener: convert.
William Rosener: addresses.
William Rosener: If we pick that same color.
William Rosener: And we'll get that Center and then i'll press save and close.
William Rosener: It mentioned there a typical you've got a right click first, then you can move it around so right click to select it, and then we can move it.
William Rosener: Okay person we're going to be connecting this command button to the javascript but we first need to add some javascript someone come right up here under tools i'm going to go script editor.
William Rosener: And that's going to open up.
William Rosener: javascript here and i'm just going to call this one test.
William Rosener: And then save some time here, let me grab a little code over here.
William Rosener: And i'll paste that.
William Rosener: Okay we're gonna have to at some point along permissions on this, but listen Let me run through this.
William Rosener: And then we'll try and execute it here, so the very first two lines appear just going to declare some variables.
William Rosener: Next, one is going to say hey we're looking in specify the sheet and that's just the active sheet, the next.
William Rosener: lines here are going to specify a ranges and unscrew about specify five ranges all of them are going to be in column seven so row 2345 and six seven let's take a look at that, why is that.
William Rosener: And that's going to be 1234567 calm seven and then i'm being rude 2345 and then right here is that six them.
William Rosener: Okay, so i've specified my ranges very first thing i'm gonna do is say okay i've got some text it's a B, C D E, F.
William Rosener: Again, will be working on a real example here pretty soon, but just to kind of walk through string manipulation and javascript so the very first thing we're going to do is we're going to use some text here it is.
William Rosener: Some string that sub string so this would be kind of the variable how many sub string specify a start and an end, you have to specify the start the end is actually optional so i'm going to specify started.
William Rosener: strings in javascript started Position zero on the starting position zero i'm going to end that position to have a notice on the end.
William Rosener: it's up to, but not including so i'm really going to get just zero and one so that's going to give me a beat so i'm going to take take the substrate which is going to be on the store that in here.
William Rosener: And i'm going to specify my range one is equal to the first two characters let's go ahead and run that i'm going to first of all press save.
William Rosener: And now i'm going to press run and the first time you do this it's going to take about 10 seconds more because it's kind of i'm going to get a message here saying that I need to allow authorization there some that sit to review permissions.
William Rosener: On the select my name i'm gonna hit advanced good untitled project and safe i'm gonna allow.
William Rosener: And i'm not sure if you heard that but shortly after doing that you're going to probably get a text message from Google saying hey you have allowed authorization.
William Rosener: Okay, so let's come back right here and we'll take a look and Sure enough, we have extracted the first two characters.
William Rosener: Now let's go ahead and connect these two i'm just going to go ahead and right click refresh on the three dots on the sign a script and i'm just going to enter a tester and press OK.
William Rosener: So at this point when I now press the command button test javascript it's actually going to execute that code again let's go back to the code here and i'm just going to continue to uncommon lines.
William Rosener: And more run up Okay, so the next one here if I want to get the middle two characters i'm going to position start in position to which is this see.
William Rosener: i'm going to go to position for but remember the end does not get position he, in order to get up to it, not including someone to get the two and three, so if I was to say this, and run it.
William Rosener: Come back here, showing up we've got the CD OK now let's get the last two characters.
William Rosener: Okay let's do flashes of just saying that this is a comment.
William Rosener: So now we're going to start a position for and go to actually six here so i'm going to start in position for which is the position that he i'm going to go to position six notice it's really going to stop at position five so i'm going to get the E and the so run that.
William Rosener: In turn up i've got to eat and the okay now that's good just the last character, and this is the more typical approach of extracting from the right.
William Rosener: And what am I going to do is i'm gonna say the text length minus one to text length, so the length of this thing is six.
William Rosener: Six minus so it'd be 123456 my string of six characters long six minus minus five and then i'm going to go to six, but again, it stopped short when shorter that so it's going to go five to five grabbing just the F.
William Rosener: In one that.
William Rosener: Internet up grab just yep Okay, no those were getting in the substrate nouns go ahead and take a look at.
William Rosener: The index up.
William Rosener: Okay i'm just going to find what position does the sea occur in here, I can see that it's going to be right here too, so a would be zero be would be one C or D position to.
William Rosener: run that.
William Rosener: comeback and sure enough it's put into two so again at this point if I wanted to I can express right here on test javascript and it's going to execute that good OK now let's start working on convert addresses So the first step i'm going to do is come right up here.
William Rosener: The top.
William Rosener: function convert addresses.
William Rosener: My little curly bracket and close it and that's going to automatically put the closing and the clothes, the started and the closing curly brackets are so I got converted addresses now it's going to save a little time here again.
William Rosener: We have a little code.
William Rosener: happy and paste it.
William Rosener: Okay let's take a look here what's going on, so just like before first couple lines here, in this case here, the first two lines is going to be declaring variables.
William Rosener: The next one is going to say we're much which sheet, am I looking at i'm just looking at the active spreadsheet and then know some specify for ranges 123 and four.
William Rosener: And what i'm gonna do is i'm going to first of all get it to work on just the first city, then we'll put in a loop and go to them 22 to 200 200 or 2000 20,000 whatever we want him.
William Rosener: Okay, so i'm taking a look here without a city comma state space than the zip code here and what i'm going to be doing is you know, initially, you probably would have thought okay i'm just going to be searching I could search for.
William Rosener: spaces, but then you're going to quickly realize hey there's some cities like little rock and Oklahoma city that there's actually a space within the city name, so we can definitely use some the space will be looking for the common.
William Rosener: OK so anyway we've got our for ranges here would be 123 and four.
William Rosener: i'm going to first of all, so i'm going to grab the value out of range number one, and when I do that it actually stores in array raised in javascript started zero so i'm gonna grab the value of 00 so, in effect, address is going to contain this information, right here.
William Rosener: Okay next step i'm gonna do is i'm going to find the position of the comma, and in this case here up, I was looking at the very first one i'm going to find it in looks like position 11th of 01234567 up to 11.
William Rosener: And someone started Position zero go done common position, but again, as we saw earlier, I stopped I specify 11 i'm actually grabbing the first 10 characters which is going to grab just the city.
William Rosener: Then i'm going to grab of the state and going comma position, plus two and the length and then, as we saw earlier, a lot of easy way to grab.
William Rosener: The information on the right is to put the length of this case minus five to the length and then i'm going to go ahead and upset those values, so let me go ahead and run it.
William Rosener: And I may have to see if that works, if not, I may have to skim along here, and I think it's probably just running the test let's go ahead and right click on this press on the dots assign a script, and this is going to be convert.
William Rosener: addresses and hit OK.
William Rosener: Now let's press on this command button and Sure enough, I can see it's working so earlier when I ran it was just still continuing to work on the test, but they're now a better connected with runs either one of them.
William Rosener: Okay, now I need to do is throw this into a loop, and to do that i'm going to go published come up here and put a verbal for counter.
William Rosener: For actually I can put it up here or a common approach.
William Rosener: is to actually specify it in part of the loop so i'm going to go there counter.
William Rosener: And i'm going to specify monster starting location and then i'm gonna go walk counter is less than or equal to.
William Rosener: This case it's 51 i'll show you here in a second why don't we go counter and, as I mentioned before, I can put either the plus plus Brian go killer equals counter plus one and let's take a look at.
William Rosener: Again, why the two and the 51 okay i'm gonna start in position to and we've got all 50 States here so i'm going to go to 51 here.
William Rosener: Okay that's the tube and and why, and while counter is less than or equal to 51.
William Rosener: Which one that's a red oh there we go here we go this needs to be a semi colon by the other so far counter equals i'm gonna have my first parameter.
William Rosener: Starting okay start my termination condition.
William Rosener: As long as it's less than 51 and then i've got counter equals kind of this one okay now here at the end, I need to put a curly bracket and notice when I put the curly bracket also put the clothes in one but that's that what was the one we want the closing one down here at the end.
William Rosener: right there so we're going to put all of this in the loop here, let me see if I can maybe if I delete some of these spaces, I can get this all on the screen at once.
William Rosener: doesn't look like the time so i've now place this in a loop a loop here i'm going to start my curly bracket and i'm going to close it now what I need to do is rather than put in our code with these tues I just need to put the founder here.
William Rosener: Okay, and I think we're ready to go so i'm going to say counter is equal to two first time on we looked at route to extract in the base data next time row, three, four, all the way up till row 51 and let me go ahead and save this.
William Rosener: will come back to Google sheets i'm gonna press convert addresses.
William Rosener: And sure not it looks like it's working perfectly there one thing which I have to mention here is some take a look here if i've added leading zero I would have to format this information.
William Rosener: The whole column, let me see if I can get that formatted.
William Rosener: As plain text.
William Rosener: Now let me try that again.
William Rosener: Okay, it works, so let me go ahead and delete that.
William Rosener: Which is still working they're not positively that.
William Rosener: And it looks like camp it's still going to populate that.
William Rosener: Okay, but one more time a convert at first of all I wanted that zip format, it is plain text.
William Rosener: Otherwise it's treated as a number and it's going to add we don't need to put zeros in front of numbers there but training us texts, we will get those numbers.
William Rosener: Okay, so just kind of wrapping up here on time here so first of all we did is we created a function for test.
William Rosener: Just kind of doing a basic some basic string manipulation their extraction and also an index of to find a position there.
William Rosener: Once we did that we kind of got this whole thing up and running, so we specified or ranges we extracted our values, and then we wrote the city in the state out in this case the range, two, three and four there.
William Rosener: And finally, again, one more time to get that connected we right click and pressed on those dots So if you want to move these around.
William Rosener: you're going to first of all want to right click, then you can reposition that otherwise when you click on it, it was wants to execute it well i'll find this video you learned about.
William Rosener: Google sheets and some string manipulation using javascript I guess thanks for the thanks.