Page History

Finding and Downloading Census Data

These are directions for downloading Census data as tables that you can edit in Excel and upload into ArcMap.

...

Most data that is useful for GIS is available in the Summary File 1 or Summary File 3 data sets. 

Formatting Long Strings of Numbers - Census Data

When Excel detects long number strings in any format other than Text or Number, it automatically displays them in Scientific format.  ArcMap will not import columns formatted as anything but General for the purposes of creating spatial joins.  Because of the DISPLAY of long number strings in General format as Scientific, ArcMap cannot read them.  This is especially problematic in data files downloaded from the Census Bureau.  Census Geographic Identifiers (ID field or Geo 2 field for data from before 2010) - crucial to joining Census data to TIGER shapefiles - always download in General format when imported to Excel. 

...

1. Highlight the ID/Geo2 column
2. In the Home Tab, navigate to Editing → Find and Select
3. Choose the Replace Option.  The Find and Replace dialog box will open.
4. Enter the value of the unwanted numbers and letters at the beginning of the string for ID/Geo2 into the Find what: field.  The value is usually 1000000US, but double check to see which portion of the ID/Geo2 field and the STFID field do not match.
5. Leave the Replace with field blank.  This will simply delete the unwanted string of letters and numbers.
6. Click Find Next.  When the program highlights a cell, click Replace.  Test this with a few cells to make sure that the program is only deleting the numbers and letters you want.
7. If the first few test cells are successful, with only the ID/Geo2 column highlighted, click Replace All.  A dialog box will appear letting you know from how many cells it deleted the specified string.
8. Click OK, then click Close.

Do the following to resolve the problem of Excel storing these numbers as scientific notations

...

1. Create a new, empty column next to the ID field (Geo 2 field for Census data before 2010).
2. Format the new column as General.  Name it Geo 3
3. Type in the formula =Left(cell# of cell in Geo 2 field, #of digits of number)  This will convert the number to a regular, General format.
4. Copy/paste the formula into every cell adjacent to a Geo 2 cell you need to convert
5. Copy the entire newly formatted set of fields in the new column
6. In same column, with all fields still highlighted, right-click and choose Paste Special.  Choose Values from the available menu.
7. Click OK.  This should paste the actual Value of the Geo 2 field in General format into the new column and display it normally, rather than as a Scientific format.
8. Use the Geo 3 field in your Spatial Join.
9. This procedure will save you the effort of having to copy/paste the value of hundreds of Geo 2 cells.

Distribute Block, Block Group, County and State Data from a Single Cell into Multiple Cells in Different Columns

Look for the Microsoft Help document, Distribute the contents of a cell into adjacent column.

Preserving Leading Zeros and Keeping them Formatted as Text

Most STFID or GEOID numbers are stored as Text in the Tiger Line Shapefiles you use to visualize the boundaries of states, counties, and cenusus tracts.  In the files you download from the Census Bureau, these numbers are automatically converted into numbers by Excel.  This means that, when you try to join your data to the shapefile for analysis, you won't be able to properly join according to the geographic identifier attribute.  There is a quick solution to this located here:

http://www.facebook.com/topic.php?uid=98595894086&topic=17703

Once you follow the steps to get the leading zeros to remain, you must then create a new column and format it as text.  Then copy the data from the calculated field (=TEXT (A2, "00000") and use the "paste special" command to paste the values into the new, text-formatted column you created.