While generating a table via CSV import, I noticed that Excel added to some table names an ending underscore: e.g. abc12.csv became Table abc12_. This does not seem to be consistent with official rules laid out here:
After many tries, I figured out that table names can be as short as 1 character if they contain only [a-z], however, once a digit is added at the end, a minimum of 5 characters is accepted. And if there are more digits, even more
Eg: name Table a is acceptable. Name Table a1 is NOT acceptable. The shortest acceptable name is Table abcd1. However Table a1234 is NOT acceptable either, the shortest acceptable name is Table a1234567.
Is there any documentation that describes this behavior and other rules for the usage of characters and digits in table names in detail? Or is what I described just an undocumented bug?
It has to do with not confusing these table names with cells. For example, if you're limited to 16,384 columns, a through xfd, it makes sense that abcd1 cannot refer to a cell since abcd is after xfd.
Similarly, a1234567 cannot be a cell reference since the row count is limited to 1,048,576. But a1234 definitely can be a cell.
Both a1 and a1234 (the two ones you give as problematic) could refer to cells. That's also supported by your a/a1 case: a is not a cell but a1 is.
The easiest way to check this by having two import files, xfd1.csv and xfe1.csv. Then see what happens when you import them.
In fact, I've just done that (albeit in Office 2013) and found it resulted in the two tables xfd1_ and xfe1. So that does appear to confirm it as the reason.
So why is this needed? Think of what would happen if the (mythical) "'Always Be Closing' Sales" company had an Excel spreadsheet containing all their income for each year (in a single workbook for simplicity).
It would therefore have a named two-dimensional table abc2023 that contained all their income for that particular year, where each row of the table contains a single month, and each column of the table contains the day within that month.
What would Excel be expected to give you when you enter the formula =sum(abc2023) somewhere else in your workbook? Should it just add up the single cell abc2023 or should it sum all cells in the table abc2023?
The question is obviously moot since Excel disallows a table called abc2023 (or any other potential cell reference clash) but that's almost certainly why it disallows it.
As to where it's documented, the very page you link to has this bullet point under Important notes for names:
Z$100 or R1C1.