Add Dual Zip Code with airSlate SignNow

City/state/zip* Other Phone Form

Music hello today we're going to show you a third method of how to split a cell of address data into separate cells for Street city state zip code and zip code extension now just a quick note on these formulas if you get the following error it means that the regional settings of your computer require a semicolon here instead of a comma I'll note those instances throughout the video ok so let's jump right in first we'll highlight our column of data here and we want to eliminate the spaces after the commas in our data so we'll click ctrl F to bring up the find search box come to a place and we will replace all comma spaces with a comma replace all and close we'll make sure our data is highlighted and we'll come to the data tab text to columns delimited next by comma and finish and now we'll split that data into two fields according to where the comma was now we want to split this field into city state and zip code so we'll come to the next cell over and in this case we'll tell it to use the last 13 digits from the right-hand side in other words the nine digits for the area code the dash the space and two more digits for the letters of the state will enter in equals right parenthesis this cell b2 comma or semicolon then 13 closed parenthesis and enter and it's kept those 13 digits from the right-hand side now to perform the same function for the rest of this column we'll highlight this cell hover our cursor over the little square in the bottom right hand side and double click it now you'll notice that the correct values show up but actually within each of these cells we don't have the values themselves but rather the excel formula to create those values so let's right click to highlight this column copy and paste as values this icon with the 1 2 3 and now those cells actually contain the values themselves and not the formulas now we'll calculate at which point the state and zipcode data start in each cell of column B since we have different city names of different lengths we'll get different results for each cell so we'll type equal find parentheses the data in situ comma or semicolon and look for it in B 2 close parenthesis and enter and in this case there are seven letters in San Jose and two spaces so the state and zip starts on the tenth character now we'll add to this formula left parenthesis the data in b2 comma or semicolon and we'll enter in -1 at the end of the formula to remove the final space close parenthesis and enter and now we've got our city value so we'll highlight this cell and apply that formula to the rest of our data copy and paste as values and we no longer need this column last we'll easily separate the state and zip we'll right-click and cut insert cells then we'll go to text to columns delimited separate by space and finish then highlight text to columns delimited and this time we'll separate according to other and a - finish and there you go now we've got street address city state zip code and the four-digit zip code extension all neatly separated in their own columns Music