...
You will first need to remove the unnecessary annotation at the beginning of the number string in the ID field (or Geo 2 field) so that it will properly match the STFID field in Census Bureau shape files.
- Highlight the ID/Geo2 column
- In the Home Tab, navigate to Editing → Find and Select
- Choose the Replace Option. The Find and Replace dialog box will open.
- 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.
- Leave the Replace with field blank. This will simply delete the unwanted string of letters and numbers.
- 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.
- 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.
- Click OK, then click Close.
Do the following to resolve the problem of Excel storing these numbers as scientific notationsTo resolve this problem, do the following:
- Create a new, empty column next to the ID field (Geo 2 field for Census data before 2010).
- Format the new column as General. Name it Geo 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.
- Copy/paste the formula into every cell adjacent to a Geo 2 cell you need to convert
- Copy the entire newly formatted set of fields in the new column
- In same column, with all fields still highlighted, right-click and choose Paste Special. Choose Values from the available menu.
- 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.
- Use the Geo 3 field in your Spatial Join.
- This procedure will save you the effort of having to copy/paste the value of hundreds of Geo 2 cells.
...