You sometimes get data where the codes use numerals, but these have gotten incorrectly defined as numeric variables. They're really string (text) variables. Typically, leading zeros have been stripped (or were left out during input) and must be restored. Also, if you need to match certain codes to comparable string variables in an ArcGIS attribute table, you must have a variable in your own incoming data that's defined also as a string variable (e.g., to Join by Attributes). Finally, you may need to concatenate values in serveral separate fields into a single string, to allow such matching in ArcGIS.
The following example shows numeric strings with leading zeros stripped, all defined as numeric (right-justified) rather than text, and with decimal points. If the GEO_ID2 field weren't there, we'd need to clean up columns C, D, and E, and then concatenate them into a single text string, for instance if we needed to perform a Join procedure in ArcGIS that requires a text attribute value with a complete 11-digit ANSI code.
NOTE: Besides cleaning up the numerals in the example below, you'll want to delete Row 2 (but save any information on the meaning of variable names). ArcGIS just recognizes one header Row.
1. Type a number in an extra cell (e.g., 100) that has the number of zeros you need to pad on the right, or the number of places you need to move a decimal point to the right. e.g., multiply by 100 to add 2 zeros to the end or to eliminate the decimal from recent tract numbers that may have a 2-digit extension (the full 6-digit ANSI/FIPS code for these will end in "00" if they don't have an extension).
2. Highlight that number and CTRL-C (the cell will be flashing); Paste Special into contents of column with wayward numerals using "multiply" to multiply 100 by those numbers
|
3a. If you now have the correct number of characters, and just need to convert to text, skip to the instructions for converting a string of numerals to text. 3b. If you still need to pad your strings with leading zeros, see steps immediately below. |
|
|
1. Type a big number in extra cell (e.g., 1000) that has enough zeros for the number of characters you need for code (e.g., 3 zeros to pad a 3-digit FIPS/ANSI code with leading zeros if missing). 2. Highlight that number and CTRL-C (the cell will be flashing); Paste Special into contents of column with wayward numerals using "add" to add 1000 to those numbers. |
 |
| 3. Insert a blank column if needed; create a text string using the "Right" function in a blank column; cells must be defined "general" or formula will paste rather than values. Use as arguments a value from the newly added added column (e.g., 1009) the number of digits needed (e.g., 3 for 3-digit FIPS/ANSI county code); result should be left-justified (it's seen as text, since a text formula was used) |  |
| 4. Copy that function throughout the rest of the blank column using Paste Special, choosing "values" (It may import OK as text into ArcGIS 10 without changing from formulas to values, but this is neater and guarantees it will be seen as a text string). Little green triangles in the cells mean that Excel is warning you that you have a string of numerals that are defined as a text string rather than as numeric. |
|
5. Delete cell that you copied the number (e.g., 1000) from. If it's the only thing in a column, it will import as a column of mostly <null> values.
2. Copy that function throughout the rest of the blank column using Paste Special, choosing "values" (It may import OK as text into ArcGIS 10 without changing from formulas to values, but this is neater and guarantees it will be seen as a text string). Little green triangles in the cells mean that Excel is warning you that you have a string of numerals that are defined as a text string rather than as numeric.
