Ask Your Question

Calc: Referencing a directory/file name with an apostrophe in it [closed]

asked 2016-12-14 23:07:47 +0100

redwdc gravatar image

updated 2020-09-16 17:20:17 +0100

Alex Kemp gravatar image

I use 1 file per year with 14 28 day sheets (over lap of months). I'm trying to copy the data from one file to the next.

However, since the directory they are in is called RED's (with the apostrophe) I get Err.508 in the cell. On the formula bar I see:


I tried putting double quotes " around the directory and file name i.e.


But that didn't work. As a test I renamed the directory to REDs and it worked. Other programs/computers reference RED's so I'd rather not rename permanently it if possible.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-16 17:20:07.809683

3 Answers

Sort by » oldest newest most voted

answered 2016-12-15 17:03:36 +0100

pierre-yves samyn gravatar image


According to my tests it is enough to double the quote.

='file:///C:/Users/pierre-yves/Documents/Red''s/Untitled 1.ods'#$Sheet2.A3


edit flag offensive delete link more


Did you also try whether the external reference is correctly generated when selected with the mouse?
(It isn't! Is this a bug? Should a dev worry about it? Who should report it? I insist on the advice I gave in my answer. It's so simple.)

Lupp gravatar imageLupp ( 2016-12-15 17:14:53 +0100 )edit

BTW: I tested with V5.2.2.2 and could verify the "double-the-apostrophe" advice. A subsequent Ctrl+Z to look again on the previous state messed up the reference completely.

Lupp gravatar imageLupp ( 2016-12-15 19:24:38 +0100 )edit

@Lupp I'm sure the external reference is correct. Also it isn't a bug Pierre-Yves answer works. Also I'm using sorry I forgot to put that in my question.

redwdc gravatar imageredwdc ( 2016-12-16 00:25:25 +0100 )edit

answered 2016-12-15 03:36:02 +0100

robleyd gravatar image

Try replacing the apostrophe with %27 - see

edit flag offensive delete link more


@robleyd LO changes the %27 to a double apostrophe. However, I do thank you for this and will keep it in mind if I ever have similar problems in the future.

redwdc gravatar imageredwdc ( 2016-12-16 00:34:39 +0100 )edit

answered 2016-12-15 14:51:48 +0100

Lupp gravatar image

updated 2016-12-15 14:58:01 +0100

Ther best idea would surely be to omit the naming of folders, files and sheets not regarding any reasonable syntax.

A folder name like "Red's" (the apostrophe) will cause problems again and again. Just a little less awful are sheet name starting with digits and/or containing spaces and non-word characters ...
I wouldn't waste an hour to find a workaround.

A reasonable syntax for names you may use if you want to avoid the kind of problems should be:
(1) Start with a letter.
(2) Continue with zero or more characters from the set including letters, digits and the underscore.

The respective RegEx would be [a-zA-Z][a-zA-Z0-9_]*. This may look "old style". Old style is best with this respect. It is still the mandatory syntax for names in relevant programming languages. (Using RegEx in LibO Calc functions will not distinguish by upper/lower case.)

edit flag offensive delete link more


@Lupp I'm aware of naming nomenclature in Windows. This is the first time I'm had a problem with an apostrophe that I wasn't able to figure out on my own.

redwdc gravatar imageredwdc ( 2016-12-16 00:38:34 +0100 )edit

I know that Winows pretends to handle these things and so does LibO concerning sheet names. I wanted to emphasise that this is bad. I personally never use apostrophes, or even spaces in any names, and I don't start sheetnames with a digit, but I am reminded of the "marginal" problems now and then when I find a request for help. Yours surely wasn't the last one.

Lupp gravatar imageLupp ( 2016-12-16 09:15:49 +0100 )edit

Question Tools

1 follower


Asked: 2016-12-14 23:07:47 +0100

Seen: 166 times

Last updated: Sep 16 '20