Convert text data to numeric data

You have a feature layer with numeric values stored as text. You need the values to be formatted as numbers instead, so you can symbolize them properly and use them to perform calculations.

You can't convert the field type from text to numeric, but you can still use the data. In this tutorial, you'll create new numeric fields and populate them with the data from the text fields. You'll learn how to choose a field type and how to calculate fields.

This tutorial was last tested on May 19, 2023, with ArcGIS Pro 3.1 . If you're using a different version of ArcGIS Pro, you may encounter different functionality and results.

Requirements

• ArcGIS Pro (see options for software access)

Convert text data to numeric data

Video

This tutorial is also available as a video.

Convert text data to numeric data video

Calculate a new numeric field

You have a layer with tax assessment values for the town of Neepawa in Manitoba, Canada. You want to use it to display assessed values per acre. The layer includes the data that you need to make this calculation, but it is stored as text, not numbers.

First, you'll reformat the assessment values.

1. Download the Neepawa project package.

   A file named Neepawa.ppkx is downloaded to your computer. A .ppkx file is an ArcGIS Pro project package and may contain maps, data, and other files that you can open in ArcGIS Pro.

2. Locate the downloaded file on your computer. Double-click Neepawa.ppkx to open it in ArcGIS Pro. If prompted, sign in with your ArcGIS account.

   Note:

   If you don't have access to ArcGIS Pro or an ArcGIS organizational account, see options for software access.

   A map appears, showing property parcels in Neepawa.

   

   Note:

   The data in this map is a subset of the Manitoba Property Assessment Information layer from the Manitoba Government.

3. In the Contents pane, right-click the Neepawa Property Assessment layer and click Attribute Table.

   

4. Review the attribute table.

   The Total Value field contains the assessment values. However, it looks like it may be formatted as text instead of numbers, because each cell contains a $ character.

5. In the attribute table, point to the Total Value field header.

   

   A pop-up appears, listing the field's type as Text (20). The number 20 refers to the number of allowed characters.

   Tip:

   The formatting of a field can also help you determine its type. Text fields have left-aligned values. Numeric fields have right-aligned values.

   You need the data in this field to be stored in a numeric format instead of text, but you can't change a field's type. Instead, you'll make a new field and populate it with the numbers from the Total Value field.

6. On the attribute table's toolbar, click Calculate.

   

   The Calculate Field window appears. This tool will calculate the values of an existing or a new field.

7. For Field Name (Exiting or New), type Assessed Value and press Tab.

   Because you have named a field that doesn't exist yet, you also have to choose a field type. Field types determine what kind of data can be stored in the field.

8. Point to the Field Type parameter and point to the info button.

   

   A window appears, describing the field type options. There are four numeric field types: Float, Double, Short, and Long.

   

   To choose the best one, consider the following two questions:

   • Do you need to store decimal values? No. The numbers in the Total Value field have no decimal places, so you can use one of the integer data types: Short or Long.
   • What is the range of your data? The most expensive parcel has an assessed value of 34 million dollars. This is too big a number to be stored by the Short field type, so you must choose Long.

     When choosing a field type, try to choose the type with the smallest size that is sufficient for your needs.

     Note:

     Read more about numeric data types at ArcGIS field data types.

9. For Field Type, choose Long (32-bit integer).

   In this instance, it doesn't matter which Expression Type you choose.

   

10. Scroll through the Fields list and double-click Total Value.

    The Assessed Value box populates with the text !Total_Value!.

    

    Note:

    If you changed Expression Type to Arcade, the Assessed Value box will instead read $feature.Total_Value. Both options will work.

    If your computer uses a locale other than American English, the expression may fail due to different currencies or thousands separators. To avoid this issue, set the Expression Type to Arcade and paste the following expression:

    var numberOnly=Replace($feature.Total_Value, '$', '') // Remove dollar signs.
    Replace(numberOnly, ',', '') // Remove commas.

    The new Assessed Value field will be calculated to contain the values from the Total Value field, but they will be formatted with the Long field type, instead of text.

11. Click OK.

    A message window appears. It includes a warning that the new field has been renamed from Assessed Value to Assessed_Value. The field was renamed because field names cannot include spaces. The field's alias still includes the space, and it is the alias that appears in the attribute table.

12. Close the message window.
13. In the attribute table, review the new Assessed Value field to confirm that it contains the values you expect.

    

    The values in the Assessed Value field should be the same as those in the Total Value field, but without a $ symbol or comma.

Calculate a new field with an Arcade expression

Next, you'll reformat the area values. You'll create another numeric field and populate it with the values from the Frontage/Area field. This time, you need to write an Arcade expression to copy the data correctly.

1. Review the attribute table for a field containing area values.

   The Frontage/Area field contains the information that you need. However, it contains a mixture of words and numbers, which means its field type must be text. You'll make a numeric field to store just the numbers.

2. On the attribute table's toolbar, click Calculate.
3. In the Calculate Field window, for Field Name (Existing or New), type Area Acres and press Tab.

   Next, you need to choose a field type.

   • Do you need to store decimal values? Yes. The Frontage/Area field values all have two decimal places, so you need to choose one of the field types that supports fractional values: Float or Double.
   • What is the range of your data? The largest parcel is 185 acres, so the smaller data type Float will be large enough.
4. For Field Type, choose Float (32-bit floating point).

   

5. For Expression Type, choose Arcade.

   You can complete this field calculation using either option for Expression Type. For this tutorial, you'll use Arcade.

6. In the Area Acres = box, delete any existing text.

   You want to copy the numeric information from the Frontage/Area field and leave behind the text information. There are several ways to achieve this, but in this tutorial, you'll use the Split function.

7. Click the Helper Type button. In the menu that appears, click Text.

   

   The Helpers list filters to those functions that are relevant for text data.

8. In the Helpers list, double-click Split().

   

9. In the Area Acres = box, place the cursor inside the parentheses. In the Fields list, double-click Frontage/Area.

   

   The expression now reads Split($feature.Frontage_or_Area).

   The Split function splits a text value at a specified character and returns an array of text values. The values in the Frontage/Area field all contain a number, followed by a space, followed by a word (for example, 0.15 ACRES). You can split these values at the space to separate the numbers from the words.

10. In the Area Acres = box, place the cursor before the closing parenthesis and type ,' '.

    Be sure to include a space between the two quotation marks, since this part of the expression indicates where to make the split.

    

11. At the end of the expression, type [0].

    

    This part of the expression indicates which item in the array should be returned. For example, if you want to return the text after the split, you can write [1] instead.

    The final expression is Split($feature.Frontage_or_Area,' ')[0]. It translates to Split the Frontage/Area field any time there is a space. Return the text before the first split.

    Note:

    There are more optional parameters to the Split function. You can read about them on the ArcGIS Arcade Function Reference page.

    To write the same expression with Python 3, type !Frontage_or_Area!.split( )[0].

    If your computer uses a locale that uses commas for decimal separators, use the following expression instead:

    var frontageArea=Replace($feature.Frontage_or_Area, '.', ',')
    Split(frontageArea,' ')[0]

12. Click OK.

    A message window appears, displaying the same warning as before.

13. Close the message window.
14. In the attribute table, review the new Area Acres field and confirm that it contains the expected values.

    

    The Area Acres values should match the numbers in the Frontage/Area field.

Calculate geometry

You may have noticed that the Frontage/Area field stores two kinds of values. The rows with the word ACRES store area, and the rows with the word FEET store frontage, which is the length of the parcel along a street.

Note:

You can confirm the frontage and area values on the map using the Measure and Explore tools.

This means that many of the values in the Area Acres field are wrong: they do not represent area, and they are not measured in acres. You can't convert the feet values into acres because one unit measures distance and the other area. However, you can calculate the area for these missing values from the features' shapes.

First, you'll select all of the parcels that have a frontage measurement.

1. On the attribute table's toolbar, click Select By Attributes.

   

2. In the Select By Attributes window, use the menus to build the clause Where Frontage/Area contains the text FEET.

   Tip:

   In the third menu, type the word FEET.

   

3. Click OK.
4. Review the attribute table to confirm that all of the FEET values are selected and none of the ACRES values are selected.

   

   Next, you'll perform a calculation on the Area Acres field. Because a selection is active, the calculation will only be applied to the selected rows.

5. Right-click the Area Acres field header and click Calculate Geometry.

   

   The Calculate Geometry window appears. This tool can measure the length, area, perimeter, and other geometry attributes of features from their shapes.

6. For Property, choose Area (geodesic).

   Geodesic measurements take into account the curvature of the earth.

7. For Area Unit, choose International Acres.

   

   Leave the Coordinate System field blank. It does not matter which coordinate system you choose, because the measurements are geodesic.

8. Click OK.

   In the attribute table, review the Area Acres field to confirm that the selected values have been edited.

   

   New area values have been calculated for the selected rows. These were measured from the map, rather than calculated from another attribute. All of the selected rows have six decimal places, while the unselected rows have only two.

9. On the attribute table's toolbar, click the Clear button to clear the selection.

   

10. Close the attribute table.

Symbolize a map with the new fields

Finally, you'll symbolize the map with the new fields that you created.

1. In the Contents pane, right-click Neepawa Property Assessment and click Symbology.

   The Symbology pane appears.

2. For Primary symbology, choose Graduated Colors.

   Graduated Colors symbolizes quantities, so it only works with numeric fields.

3. For Field, choose Assessed Value. For Normalization, choose Area Acres.

   These field choices symbolize each parcel with its value divided by its area.

   

   On the map, the red parcels have the highest value per acre.

   

4. On the Quick Access Toolbar, click the Save button.

   

5. Close the Symbology pane and close ArcGIS Pro.

Field types can't be converted in ArcGIS Pro, but new fields, with new types, can be created and calculated from existing fields. In this tutorial, you learned how to identify and choose field types, how to calculate fields and geometries, and how to construct an Arcade expression to isolate part of a field's value. You created two numeric fields to replace two text fields.

You can find more tutorials in the tutorial gallery.

Acknowledgements

• Neepawa Property Assessment: derived from Manitoba Property Assessment Information from the Government of Manitoba, licensed under the OpenMB Information and Data Use License (Manitoba.ca/OpenMB).
• World Topographic Map sources: Esri, HERE, Garmin, FAO, NOAA, USGS, OpenStreetMap contributors, and the GIS User Community