English

How To: 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

To perform a successful join operation for fields that contain a long string of numbers and hyphens, ensure the following criteria are met:

  • 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.
The instructions provided below explain how to convert an ID field containing long numbers with dashes to a text field in Excel:
  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.
This formula removes the hyphens, but retains the values of the field as a text field data type.
  1. Select and apply the formula for the rest of the columns containing the ID values.

Related Information