Write Multiple Zip Code with airSlate SignNow

Print individual zip code

hey this is ralph and in this video i want to see if we can extract the zip code from a multi-line address field in excel so i had this question a couple days ago in youtube comments from a previous mail merge video and they were asking about getting that zip code from a multi-line field so this could definitely happen sometimes if you're getting your excel data from some other database and perhaps the address field is one complete unit however it's probably going to be in your best interest to break that address up into the key parts if reasonable to do so it could be tricky depending on how that data is structured with spaces and things like that so i've made myself a little demo here with a few fake addresses and i used three lines for each one to throw a little curveball at it i did put an extra four characters after one of the zip codes because the goal here is going to be to extract only the five digits of the primary zip now if you were just doing a straight old mail merge perhaps you can just use the multi-line address no problem but it's not unusual to want to be able to extract a key piece of information especially if i wanted to sort these by zip code or something maybe they need to be distributed amongst different sales people maybe we want to do a mailing only to a certain zip code and stuff like that now i haven't successfully tried this yet but i'm about to however the first thing i might try to do is just flash fill which is pretty darn nice so with flash fill you kind of write how you want you the the data you want extracted and see if excel can figure it out for you so i've got a zip code field off to the side and i'm going to go to my first address and just type in three three five nine two three three five nine two there we go let me go back to that cell go to my data ribbon and i'm just gonna click flash fill and see if excel can help me out unfortunately it couldn't it didn't you know it did well at other things if all the addresses were in the same format it probably would have been pretty pretty fine right there and we would call this job done but since there is a little bit of a screw up here with or not screw up but there's a little bit of deviation with that uh four digit extension not gonna work for us today so flash fill is cool when it works okay so let's do this a little bit more in manual so again i'm gonna pick on one of my dates here i'll pick on the from one of my dates one of my addresses i'll pick on the first one and i'm going to use the combination of functions now first thought might be well great equals right so i can do a write function for the address five characters and that's gonna work well but again we're gonna have trouble when we get to those addresses that have that extra four letter so just using the right function isn't going to be very effective here so what other common element can i use well if all of my zip codes are following the date that's got potential let me make this a little bit wider so when i start typing this function it could look pretty messy by the way so i've got that spacing in there so the problem is there's so many things to look at i can't just look at the uppercase letters of the state i need to look at the spaces themselves now something that i can start to look at is these addresses only have one comma so you want to kind of find that key piece of information in the address that you can grab onto that the function can grab onto that comma is going to come in pretty darn handy especially if it looks like after every comma there's a space two characters a space and then the zip code that i want so let's see i could try something like this equals mid the text in question is going to be the full address comma now my starting position my starting position is going to be a find function i'm going to find in quotes the comma within the address field all right now i'm going to just go with that closing parentheses i'm closing off the find function comma and now the number of characters we're just going to guess at this one first we'll refine it in just a moment and let's see i'll say i want five characters which is probably actually going to be what i end up using closing parentheses finishes my mid function and and now we can start to see what little mess we're getting into all right well that's pretty good i am finding the comma and i'm displaying some characters i'm getting the comma the space the two letters and then the space after that so that's my five so let me go back to that first function and see if i can't tweak this now my starting position for the mid function is currently the comma i'm finding that comma but i want to start at the comma i want to start after the comma let's look at one of my addresses so i've got the space and then two letters that's three and then another space that's four so if i do a plus four right after that find it gets me closer it gets me closer for sure but i think it's not quite accurate i think i'm still getting that space in there so maybe i need to go five after the comma because it's counting the comma as well now this is looking pretty good okay so it looks like this is going to work out for me now of course big big requirement here is that all of my addresses even though they're multi-line they're still structured in that same way consistently what could throw this off well what if there was another comma in here somewhere what if one of these was a business name that had a comma in it or maybe you had two people's addresses in there so that could certainly make it a little bit more complicated you're going to require another level of fix however what we've got here is a mid function and one of the keys to this is we are starting in the middle of our big text string and we're looking for that one thing in this case it was a comma and then we want to start several character positions after that comma and then display five characters of that for our five character zip code so hopefully this helps that um one questioner and yeah so i'm using a mid and find function to extract the zip code from a multi-line address field once we've got that then we can of course sort by zip code and create new worksheets based on certain zip codes all right thanks for hanging out with me take care