Join Function

[adamscountyGIS]

I'm trying to join a field in a shapefile in which the data has no dashes or dots ( 1830000024000) to an excel table field that does have dashes and dots (183-00-00-024.000).  Is there a way to do this?

[DavidSolari]

Joins can't transform the data during the join process so you'll have to create another field on one side of the join and then populate it with the right format.

[SteveCole]

It might be easiest to do this on the Excel end and then join the results back in Pro. In the Excel spreadsheet, create another column in the spreadsheet and use Excel functions like SUBSTITUTE to convert the dashes and dots to nothing, so something like this:

---

=SUBSTITUTE(A2, "-", "")

The example above uses Letter/Number cell reference format but I prefer Row/Column (RC, RC[-1], etc) which is more generic and you can quickly do an entire column of conversions after crafting the formula on the first row. You'd have to do it one more time to address the dot but then you would be left with a column that matches your GIS field. Excel gets finicky here so be sure to format the column as TEXT because if you have an entry with leading zeros in it, those will drop off when coming back into GIS (at least that's been my experience when dealing with field values of numbers that you need treated as text).

[adamscountyGIS]

That is what I thought but wanted to double check.  Thank you so much!

[KimOllivier]

Why are you using shapefiles and excel tables? You would be much better off using a proper database format with tables. Then you can validate the data and control the data schema. It will be faster and more robust. It will handle the difference between blanks and null values.

You can use the Microsoft schema.ini file to properly define the schema for the excel spreadsheet before you import it to a database table. Once in a database the keys you are going to use for the table join can be indexed for speed, set to NOT NULL to make sure they are always populated and generally document the schema.

The codes with dashes have to be strings, but that may need to be explicitly set.