This can be done with openpyxl, I'm not sure its possible with Pandas at all. Here's how I've done it:

import openpyxl

wb = openpyxl.load_workbook('yourfile.xlsm')
sheets = wb.sheetnames
ws = wb[sheets[0]]
# Deprecation warning
# ws = wb.get_sheet_by_name('Sheet1')
print(ws.cell(row=2, column=1).hyperlink.target)

You can also use iPython, and set a variable equal to the hyperlink object:

t = ws.cell(row=2, column=1).hyperlink

then do t. and press tab to see all the options for what you can do with or access from the object.

Building on the approach by @guillaume-jacquenot we can use apply to apply this to an entire Series.

df = pd.DataFrame({'Year': [2000, 2001, 2002 , 2003]})

For cleanliness, I wrote a helper method.

def make_hyperlink(value):
    url = "https://custom.url/{}"
    return '=HYPERLINK("%s", "%s")' % (url.format(value), value)

Then, apply it to the Series:

df['hyperlink'] = df['Year'].apply(make_hyperlink)

    Year    hyperlink
0   2000    =HYPERLINK("https://custom.url/2000", "2000")
1   2001    =HYPERLINK("https://custom.url/2001", "2001")
2   2002    =HYPERLINK("https://custom.url/2002", "2002")
3   2003    =HYPERLINK("https://custom.url/2003", "2003")

What I would do is use openpyxl to get the hyperlinks, then iterate through the rows in a for loop, creating a list of the hyperlinks, and add a new column in your pandas dataframe:

import openpxyl
import pandas as pd

df = pd.read_excel('file.xlsm')

wb = openpyxl.load_workbook('yourfile.xlsm')
ws = wb.get_sheet_by_name('Sheet1')
print(ws.cell(row=2, column=1).hyperlink.target)

links = []
for i in range(2, ws.max_row + 1):  # 2nd arg in range() not inclusive, so add 1
    links.append(ws.cell(row=i, column=1).hyperlink.target)

df['link'] = links

Same first bit of code as I answered in the other question

Probably you need a comma (,) instead of a semi-colon (;) in the formula. This is because Excel stores formulas in US-style syntax (see Non US Excel functions and syntax in the XlsxWriter docs).

When I run your formula through XlsxWriter I get an Excel warning about "We found a problem with some content in 'demo.xlsx'" and when I click on "yes" to recover the formula is zero, as your described.

Changing the semi-colon to a comma makes the program work without warning and as expected:

import xlsxwriter

workbook = xlsxwriter.Workbook('demo.xlsx')
worksheet = workbook.add_worksheet()

link = '=HYPERLINK(\"{0}/tr_events.php?triggerid={1}&eventid={2}\", \"{3}\")'.format('www.foo.com', 'abc', 'def', 'event1')

worksheet.write('A1', link)

# Or with a hyperlink format.
url_format = workbook.get_default_url_format()
worksheet.write('A2', link, url_format)

workbook.close()

Output:

You can use the import xlsxwriter library to add hyperlinks. Speaking of hyperlinks, the example here shows some examples such as:

worksheet.write_url('A5', 'http://www.python.org/', tip='Click here')

But, if you don't want to manually write a line of code for each cell, then you can loop through and add the hyperlinks dynamically if you have a list of all the hyperlinks.

hyperlinks = ['a.com', 'b.com', 'c.com' ... etc.]

for i in range(1, len(hyperlinks)):
    worksheet.write_url(f'A{i}', hyperlinks[i-1], tip=df['column string'][i-1])

Your hyperlinks would obviously have to be in the correct order in the list, or you could create a dictionary that makes the text and hyperlink a key-value pair and use .map to bring the hyperlink into your dataframe as a column. Then you could sort the values and send the hyperlink to a list with hyperlinks = df['hyperlink'].to_list(). Then you could run the for-loop.

But, I think you will have to create a list or dictionary first.

Also, check out this answer for reading in date with hyperlinks using openpyxl:

Pandas read_excel with Hyperlink

And, this one for writing data with hyperlinks using pandas:

add hyperlink to excel sheet created by pandas dataframe to_excel method

I'm not sure that pandas is capable of parsing Excel hyperlinks.

So, as a workaround, you can use openpyxl.worksheet.hyperlink module to get a list of the hyperlinks in a worksheet/column then create a series in your dataframe based on this list.

Try this :

from openpyxl import load_workbook
import pandas as pd

wb = load_workbook('test.xlsx')
ws = wb['Sheet1']

list_of_links = []
for i in range(2, ws.max_row + 1):
    try:
        list_of_links.append(ws.cell(row=i, column=1).hyperlink.target)
    except AttributeError:
        list_of_links.append(np.nan)
        
df = pd.read_excel('test.xlsx')
df['Links'] = list_of_links

# Output:

print(df)

          ColA                          Links
0          NaN                            NaN
1          NaN                            NaN
2          NaN                            NaN
3  Link_text_1       http://www.something.../
4  Link_text_2  http://www.something_else.../
5          NaN                            NaN

# Worksheet used :