I've had a polygon shapefile joined to an XLS file with no issue until today. Although the category of the column category is designated as "Number" in Excel, ArcMap recognizes the column as "String" when the table is added into Arc or when a Join is performed. This is good since it allows for the Join to be performed.
However, something must have happened today as Arcmap now reads the column as "Double". This of course prevents the Join between the shapefile and XLS. I suspect I entered some value incorrectly, but I can't seem to find the problem cell.
I firstly tried changing the column's category in Excel from "number" to "text", but that didn't solve it, nor did changing the category to "general". So I then created a new column ("B", whereas the column I'm joining with the shapefile is column "A"), and used the formula " =TEXT(A2,"0") " . ArcMap will now allow me to join the shapefile to this newly created column.
Although the problem is solved, I want to pinpoint which cell may have causing the original problem. I have thousands of 5 digit numeric values in this column which were joining with no issue before today.
My process is that I work from an XLSX file and then, when I want to see the updates reflected in the joined shapefile, I close Arc (to remove the schema lock from the XLS), and then save the XLSX to XLS. My XLSX file does have a "highlight duplicates" conditional formatting rule, but I don't think that was the problem since it has otherwise worked up to this point. Additionally, the Excel file has a "custom sort" option with two levels of sorting rules, but again that hasn't caused any issues before.
So I just want to know whether there is some way to check for which cell is causing Arc to read the column as "double" as opposed to "string" as it was before.