excel column does not appear when doing join after doing modification to the column

Question

Using ArcGIS 10.1 I am trying to add " /1 " my column provided Shp File

how it appears in the county supplied excel spreadsheet under "parcel #"

43002/1
18524/1
1666/1
4270/1

Currently, the county provided shapefile only displays

43002
18524
1666
4270

How do I ADD the /1 to the already current number within my shapefile to make it look like 43002/1 etc?

There are over 13,000 tracts so doing it by hand it out of the question. When I go into excel and delete / REPLACE, when I do the join, that parcel column does not show up, where before it did before I did the delete of the /1. I went into format, number and used zero decimal placement. Still nothing.

1. Can someone tell me how to add " /1 " to the parcel numbers?
2. Is there something I can do with excel so when I do the join, the parcel num column shows up and not disappear?

Answer 1

I suspect the issue you are running into is related to field definitions.

In the county shapefile, is parcel number a string or a number field? If number, it will have to be converted to a string - you can't have anything but numbers (and decimals) in a number field. Add a new field of type text and field calculate its values to equal your original field. You can then use a string append function in field calculator to add "/1" to the end (this may actually be all you need to do if that field is already a string).

If the county shapefile field is already a string, the join issue is with the Excel data. You can't have anything but numbers (and decimals) in a number field, so the / is simply being applied as a forumula to divide the number by one. You need to format that column to text (sounds like it is?). Inserting a singlequote before each number may work, but it might not because I believe in Excel that is an overide code and not and explicit format command.

If BOTH are already strings/text, then it's the conversion process that has an issue. ArcGIS sometimes misinterprets the data type, so you have to explicitly tell it which to use with a schema.ini file.

Other potential problems are mismatched datatypes (covered above on an individual basis - remember a join has to be done on the same datatype for it to match) or invalid field names.

Answer 2

Are those Parcel# (43002 18524 1666 4270) saved within one column?

If so, I would export the attribute table from shapefile into a text file and open it by using excel.

Then select that Parcel# field, and use the "Text to Columns" option under Data tab to separate this field into 4 different field, it should looks like:

field 1 | field 2 | field 3 | field 4 | 
43002 18524 1666 4270

After that use the concatenate function to append the "/1" at end of each field, see the screenshot below and you can see I used '=VALUE&"/1"' to append the text:

enter image description here

Then merge these four fields into a single field, save it, open by using ArcMap, and join this field back to the shapefile.

Hope this works.

Thanks.

• Zoran: There are multiple duplicate parcel numbers in the shapefile. I think it would be easier if I knew how to add the /1 to the shapefile through a script.. is that possible to do?