Structured Table Referencing and Double Brackets in Column Headers

If you use certain characters in the column names of your table, then you have to use double brackets around those names when you refer to them in formulas. I don’t care for that, so I don’t use those characters. I was converting some imported data into a table and, of course, it had spaces in the column names (and a few other naughty characters). I needed to clean up those characters before converting to a table. The formula below shows what the double brackets look like.

This office web page provides a list of characters that cause this behavior. I don’t know where they got that list, but I did notice that there was no underscore on it. I like to use underscores to separate words, but I can’t use them in tables because of the double bracket thing. I figured I’d better make my own list if I’m going to clean up data.

I wrote this code to list out all of the bad characters:

It loops through all the characters in the basic ASCII character set, inserts the character into a the column header, and reads the formula that references that column. I check for

or an apostrophe. It turns out that all the characters you have to escape with an apostrophe also cause double brackets, so I only needed to check for the double brackets.

With my list, I created a function to clean the data before I use it as a column header. I don’t check for Chr(13) because I don’t think you can have that in a cell.

Now, to make sure my Excel workbooks are utterly un-editable by anyone else, I’m going to write an event handler that converts all my underscores to something else as I type them. The only question, is should I use an elipsis (chr$(133))

or a macron (chr$(175))

That’s a tough one.

7 thoughts on “Structured Table Referencing and Double Brackets in Column Headers

  1. Hi Dick – You can have a chr(13) is a cell. It’s what XL puts there when we do an alt+enter, or one could concatenate it in with CHAR(13).

    If you switch to chrW() you have all of unicode at your disposal. Think of spreadsheets with chrW(10086): &#x2776. Or not.

    …mrt©¿©¬

  2. It only puts the double brackets in when you’re using the @ i.e. ThisRow referencing, doesn’t it? I don’t see why that’s an issue…I never type those double brackets because Excel does it for me when I use the mouse to click on the cell I want in my partially completed formula. But then I know how much you hate the mouse…

  3. I’m pretty sure Alt+Enter is Chr(10), not 13.

    Very often I’ll type INDEX MATCH or SUMPRODUCT formulas against a table on another sheet and don’t point to references. I can still point to references with the just the keyboards, but it’s easy to type with autocomplete.

  4. Dick – it is. I checked and I should have checked better. It’s OS dependent, and I checked on the, ahem, less popular OS.

Leave a Reply

Your email address will not be published. Required fields are marked *