Convert an ID field containing long numbers with hyphens to a text field in Excel

Summary

Working with an Excel table containing field values that consist of long numbers and hyphens can cause issues when the table is imported for use in ArcMap.

For example, attempts to perform a join operation in ArcMap fail when using the Parcel ID with another ID from another table (either as a number or text field), as the Parcel ID field contains the value of '17-80009-6578-4657-002'.

When formatting the Excel table for use in ArcMap, using the Find and Replace function in Excel to remove the hyphens converts the values to the scientific notation format as Excel automatically shortens the value to '1.78001E+17' due to the length. However, attempts to convert the value from scientific notation to the decimal format in Excel using the Format Cell option returns an inaccurate value such as '178000965784600000'.

Procedure

• Avoid using hyphens in the field values.
• The values must not be in scientific notation.
• The field data type for both ID fields must be the same.

1. Create a new column in the Excel spreadsheet.
2. Use the following formula for the new column created in Step 1.

   =SUBSTITUTE(B2,"-","")

   Note:
   B2 represents the cell containing the long string of numbers with hyphens.

3. Select and apply the formula for the rest of the columns containing the ID values.