Unable to calculate fields containing special characters

Description

In ArcGIS Pro, the Calculate Field tool fails to execute mathematical calculations on numeric values containing special characters including commas, hyphens, parentheses, brackets, asterisks, or units of measurement.

The tool fails and returns the following error message:

Error:
Error 000539: Traceback (most recent call last): 
File "<expression>" line <line_number>, in <module>
TypeError: can't multiple sequence by non-int of type 'float'

Cause

The values containing the special characters are text data types and stored in a text data type field. Mathematical calculations cannot be executed on text data type fields.

Solution or Workaround

Create and populate a new numeric field

To execute mathematical calculations on values with special characters, remove the special characters and store the values in a new numeric field.

1. Open the ArcGIS Pro project.
2. Create a new field with a numeric data type.
   1. Open the attribute table of the feature layer and click Add to add a new field.
   2. In the fields view, for Field Name, type the new field name and select a numeric data type for Data Type. Refer to ArcGIS Pro: Numbers for more information. In this example, the 'Length_new' field with the Float data type is created.

3. On the top ribbon, click Save in the Changes group to save the edits.

3. Populate the new numeric field with the special characters removed from the values using the strip() function.
   1. In the attribute table, right-click the new field and select Calculate Field.
   2. In the Calculate Field pane, for Expression Type, select Python 3 from the drop-down list.
   3. For Expression, insert the following code to remove the special characters. Replace <my_text_field> with the field containing the special characters and <my_character> with the special character to be removed.

!<my_text_field>!.strip("<my_character>")

4. Click OK.

In this example, the asterisk is removed from the values in the Length field to populate the numeric 'Length_new' field.

Use the int() or float() function

To execute mathematical calculations on numeric values in a text data type field without creating a new field, use the int() or float() function when referring to the text field, and remove the special characters using the strip() function.

1. Open the ArcGIS Pro project.
2. Open the attribute table of the feature layer containing the text field with the special character values.
3. Right-click the field and select Calculate Field to execute the calculations on the field.
4. Remove the special characters using the strip() function and add the int() or float() function when referring to the text field.
   1. In the Calculate Field pane, for Expression Type, select Python 3 from the drop-down list.
   2. For Expression, insert one of the following codes, depending on whether the values have decimal points. Replace <my_text_field> with the field containing the special characters and <my_character> with the special character to be removed.
      • Use the following code to calculate the values without decimal points.

int(!<my_text_field>!.strip("<my_character>"))

• Use the following code to calculate the values with decimal points.

float(!<my_text_field>!.strip("<my_character>"))

3. Click OK.

In this example, the asterisk is removed from the values, and the float() function is used in the mathematical calculation on the 'Length' field as the values contain decimal points.