PHPExcel Named Ranges not working with special characters in MS Office

Question

I created a dependant dropdown lists as shown here.

The problem is that the named ranges wont work with special characters like spaces, brackets, hyphen, etc (underscore works!) in MS Office but works fine in LibreOffice.

$objPHPExcel->addNamedRange( 
    new PHPExcel_NamedRange(
        'New Zealand', 
        $objPHPExcel->getSheetByName('Worksheet 1'), 
        'A1:A2'
    ) 
);

The name New Zealand has a space and it generates excel errors.

I need it working in Office as well.

Names in Excel can't contain spaces, or start with a number. You can always use an underscore in place of the space. — Tim Williams, Aug 04 '15 at 06:17
Yes I understand. But i need to maintain readability. And replacing all the special characters with underscore doesn't sound right. But the issue is, if it works in Libre how can i get it working in Office ? — Sankalp Tambe, Aug 04 '15 at 06:56
You can't use spaces, so you can't make it work exactly as in Libre. You're going to have to change something. — Tim Williams, Aug 04 '15 at 14:51

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

Quoting from the MS Excel documentation (amd repeating what Tim Williams has said in the comments)

The following is a list of syntax rules that you need to be aware of when you create and edit names.

Valid characters The first character of a name must be a letter, an underscore character (_), or a backslash (\). Remaining characters in the name can be letters, numbers, periods, and underscore characters.

Note You cannot use the uppercase and lowercase characters "C", "c", "R", or "r" as a defined name, because they are all used as a shorthand for selecting a row or column for the currently selected cell when you enter them in a Name or Go To text box.
Cell references disallowed Names cannot be the same as a cell reference, such as Z$100 or R1C1.
Spaces are not valid Spaces are not allowed as part of a name. Use the underscore character (_) and period (.) as word separators, such as, Sales_Tax or First.Quarter.
Name length A name can contain up to 255 characters.
Case sensitivity Names can contain uppercase and lowercase letters. Excel does not distinguish between uppercase and lowercase characters in names. For example, if you created the name Sales and then create another name called SALES in the same workbook, Excel prompts you to choose a unique name.

A name containing a space character may work in LibreOffice, but it will not work in MS Office... the only way to get this working in MS Office is to change the name so that the MS Office naming rules apply

PHPExcel Named Ranges not working with special characters in MS Office

1 Answers1