0

I'm trying to use the "=Right(A1,8)" Excel function in my python openpyxl file but all it does is place the formula text into the cell.

What am I doing wrong?

File info: I have a working file where I need to read and extract text from cell A1, (Exact Text in A1 is SITE:SC05100A) I want to strip off the "SITE:" portion and save the remainder in cell B4. In the spreadsheet I use "=RIGHT(A1,8)" which returns "SC05100A". However when I try to use that in my python script it simply puts the formula string into the cell.

Cell A1 value is SITE:SC05100A Cell B4 is currently empty.

Python code entered is:

        ws['B4'].value = '=Right(A1,8)'
        print ('A1 value is: ',ws['A1'].value)
        print ('B4 value is: ',ws['B4'].value)

when I run the code I get no errors and output shows:

        A1 value is:  SITE: SC05100A
        B4 value is:  =RIGHT(A1,8)
        [Finished in 677ms]

Yes, I have tried it without the 'B4 value is: ' portion, but as you can see that works as well.
Any suggestions are appreciated.

Danno
  • 1

1 Answers1

0

Openpyxl doesn't evaluate formula. If you open your excel file, you'll see that excel is evaluating the formula properly. If you wanted to see the evaluated value, use xlwings (you do need Excel installed for this to work)

import openpyxl as op 
import xlwings as xw

# Create workbook, add data
wb = op.Workbook()
ws = wb.active
ws['A1'].value = "ABCDEFG"
ws['B1'].value = '=Right(A1, 3)'

# Save workbook
wb.save("test.xlsx")

# Reopen with xlwings
wbxw = xw.Book("test.xlsx")
print ('B1 value is: ', wbxw.sheets['Sheet'].range('B1').value)
jezza_99
  • 1,074
  • 1
  • 5
  • 17
  • have never heard of xlwings before. Looks like it needs to be pip installed before I can give it a try. What is the difference between openpyxl and xlwings? – Danno Nov 11 '21 at 07:48
  • Openpyxl directly reads and writes the xlsx files, whereas xlwings uses excel itself to open and read/wite files (this is why using xlwings opens an excel window). A more thorough explanation can be found [here](https://stackoverflow.com/a/58331928/12664040). And yes it needs to be pip installed – jezza_99 Nov 11 '21 at 19:30