0

I need to insert a formula into Excel using Python. I am creating a dataframe and adding a column containing the formulas and then writing it into an Excel.

The formula has the format '=HYPERLINK("#'"&F2&"'!A1",F2)'

F2 is a variable and all other characters are constant. I need to dynamically generate this string.

I tried doing =HYPERLINK("#'"&F2&"'!A1",F2) but it is not working and I got '=HYPERLINK("#\'"&F2&"\'!A1",F2)' which includes the back slash and the formula does not work.

How do I create a string like '=HYPERLINK("#'"&F2&"'!A1",F2)' ?

Any help would be highly appreciated.

Ch3steR
  • 20,090
  • 4
  • 28
  • 58
Suresh Subramaniam
  • 47
  • 2
  • 8
  • You might find this to be helpful. https://stackoverflow.com/a/517372/13131172 – Can Mar 27 '20 at 06:36
  • You might want to use formatting. I'm not completely able to understand your needs, but you can surely try this. ```print('=HYPERLINK("#'"&{}&"'!A1")'.format('F2'))```. – Hades1598 Mar 27 '20 at 06:50

2 Answers2

0

Reference The method you used works with Python 3, i checked it with trinket.io

Joshua Varghese
  • 5,082
  • 1
  • 13
  • 34
0

Since you are making string dynamically. There are two things you can do.

  1. r'=HYPERLINK("#'"&F2&"'!A1",F2)' r signifies raw string (if you are not using any variable).

  2. If you are making string dynamically using other variables. Use formatted string.

    var1= '"#'"&F2&"'!A1"' var2= 'F2' formula= f'=HYPERLINK({var1},{var2} )'

print(formula)

'=HYPERLINK("#&F2&!A1",F2 )'

Prashant Singh
  • 45
  • 6
  • Thanks but print() behaves differently than when storing in a variable. For example print('=HYPERLINK("#'+"'"+'"&F2&"'+"'!A1"+'",F2)') gives me the same output as yours. But I need to store it in a variable and insert into a dataframe column. When I do s = '=HYPERLINK("#'+"'"+'"&F2&"'+"'!A1"+'",F2)' , variable 's' has value '=HYPERLINK("#\'"&F2&"\'!A1",F2)' – Suresh Subramaniam Mar 27 '20 at 19:13
  • Then this is not a problem with string creation.Looks like Pandas is adding an escape sequence when creating excel/CSV file. You need to provide file and code you are using to write an excel file. Plus you should upvote answer if you find it useful. – Prashant Singh Mar 28 '20 at 20:14