Unwanted backslash ("\") appears in a string

Question

I have the following function that constructs and returns a string (used to create a formula for Excel).

def create_hyperlink_formula(target_sheet,link_text):
    a = """=HYPERLINK("#'{target_sheet}'!$A$1),"{link_text}")""".format(target_sheet=target_sheet,
                                                                        link_text = link_text)
    return a

So by running:

create_hyperlink_formula(target_sheet = "Cover", link_text = "Return to cover page")

My desired result is as follows:

=HYPERLINK("#'Cover'!$A$1),"Return to cover page")

But instead I get this:

=HYPERLINK("#\'Cover\'!$A$1),"Return to cover page")

I cannot seem to get rid of the unwanted backslashes ("\") in this string.

I am completely lost as to why that happens and how to fix that. Even a workaround would help me a lot at this point, as string.replace() method doesn't help here either.

Many thanks in advance.

Where are you seeing the backslashes? When I run the code in Python they aren't appearing. By the way, the desired result you indicate is not a valid Excel formula. — norie, Aug 03 '21 at 15:57
Related, but not an exact duplicate: https://stackoverflow.com/questions/24085680/why-do-backslashes-appear-twice — Pranav Hosangadi, Aug 03 '21 at 15:58

score 2 · Accepted Answer · answered Aug 03 '21 at 15:57

I am assuming you are running this in the Python REPL / in the command line. The backslashes aren't actually there, it's just because running an expression will print its representation (repr(x)) not its string form (str(x)).

Try doing this in the command line, or saving it to a file or running it:

def create_hyperlink_formula(target_sheet,link_text):
    a = """=HYPERLINK("#'{target_sheet}'!$A$1),"{link_text}")""".format(target_sheet=target_sheet,
                                                                        link_text = link_text)
    return a

print(create_hyperlink_formula(target_sheet = "Cover", link_text = "Return to cover page"))

Or, you can instead try it online and observe that the result is as you want it.

Basically, the string ' needs to be represented as '\'' because ''' isn't a valid Python expression, but as a string, it's just a single quotation mark. If the string actually had backslashes, it'd be '\\\'' (or just "\\'").

Unwanted backslash ("\") appears in a string

1 Answers1