Page History

...

Hover your cursor over the small black box in the bottom right corner of the highlighted cell as shown below. Notice that your cursor changes from a thick white cross to a thin black cross.

1. Double-click the black box in the bottom right of cell F2 to copy the formula down the table.
   

...

1. 
   Image Added
   
   
2. In cell G2, use the same formula building techniques to enter “=D2/(B2-E2)*100” and copy the formula down the table.

One last problem with this worksheet, which is difficult to detect, is that there is actually a space stored after every state name. This space will prevent the table from joining properly to another table whose state names do not have a trailing space. You will now perform a series of automated operations use the TRIM function to remove this extra space.

...

The trim function removes all extra spaces from text, leaving only a single space between words and no spaces at the start or end of the text.

1. Right-click column D B and select Insert to add three a new columns column in which to work.

First, you will use the Text to Columns tool, which splits the information contained in one column into multiple columns based on a delimiter. The delimiter is the character which is used to signal when to shift the data to the next column. As a result, the delimiter itself is removed and all characters following the delimiter are shifted to the next column. In this case you will use the space as the delimiter to remove it.

1. Click column A.
2. Click the Data tab at the top of the Excel window.
3. Under the Data Tools section, click the Text to Columns button.
4. In the ‘Convert Text to Columns Wizard’, click Next >.
5. In the Delimiters box, uncheck Tab and check Space and click Next >.
6. Click Finish.
7. When asked if you want to replace the contents of the destination cells, click OK.

The extra spaces have been removed, but the state names with two words have been separated into two columns. You will need to concatenate them back together into a single column separated by a space, but only for those states with two words. Performing a concatenation on all the states with an added space in between the two words would end up adding the trailing space again. The formula below basically says, if column B is blank, then copy the state name from column A; otherwise, if column B contains text, then concatenate the first part of the state name in column A with the second part of the state name in column B and add a space in between them.

1. In cell C2, use your formula building techniques to enter “=IF(B2="",A2,CONCATENATE(A2," ",B2))”. Ensure that there is no space between the first set of quotation marks and a single space between the second set of quotation marks.

In this instance you can’t double-click the black box in the bottom right of cell C2 to copy the formula down, because there is not consistent data in the column the left of it.

1. Click and hold the black box in the bottom right of cell C2 and drag down to cell C49 to copy the formula down the table.

At this point it looks good, but the values contained in column C are actually formulas, not text, and ArcGIS will not be able to interpret the formulas.

1. Right-click column C and select Copy.
2. Right-click column D and select Paste Special….
3. In the ‘Paste Special’ window, select to Paste Values and click OK.

Now the states listed in column D are stored as text, rather than as formulas, so you can now delete columns A through C.

1. In cell B2, type “=” to indicate the start of a formula.
2. Type “=trim”.
3. Click cell A2 (Alabama).
4. Type “)” (close parenthesis).
5. Ensure your cell says “=trim(A2)” and press Enter to finish the formula.
6. Copy the formula down the table
7. Click column A.
8. Hold down Shift and click column C.
9. Right-click column C and select Delete.
10. In cell A1, type “State”.

Removing all formatting in Excel

At this point your table has everything you need and nothing extra, but it is always recommended to clear all formatting and programming , including special fonts, background colors, borders, formulas, etc., before bringing the Excel file into ArcGIS.   In addition, columns B, G, and H currently have To get rid of all formatting and programming, you will copy all of the data into a new blank worksheet.

...