how to find cell containing specific text using python?

Question

I am trying to iterate xlsx file and find the cell that contains our company's name using python. The file consists of 2 or more sheets, and each sheet has 6 company's information. Each cell I am looking for has formation as below:

Cell F6 = 1ST(Company_A+Company_B)

Cell G6 = 2ND(Company_C+Company_D)

Cell H6 = 3RD(Company_E+Company_F) and so on.

I'd like to find the cell that contains Company_A. I have done some coding, but I got some problem.

The coding I can do is as following:

import openpyxl
bid = openpyxl.load_workbook('C:/Users/User/Desktop/bidding.xlsx', data_only=True)

for sheet in bid.worksheets:
    for row in sheet.iter_rows():
        for entry in row:
            if entry.value == '1ST(Company_A+Company_B)':
                print(entry.offset(row=1).value)
                print(round(entry.offset(row=8).value/100,5))

I can find the value I want, but I want to find the cell without entering everything

Dror Av. · Accepted Answer · 2018-07-04 08:56:05Z

4

As you're using == the script is checking for the string in the cell to match exactly that. Instead use in.

Your code should be:

import openpyxl
bid = openpyxl.load_workbook('C:/Users/User/Desktop/bidding.xlsx', data_only=True)

for sheet in bid.worksheets:
    for row in sheet.iter_rows():
        for entry in row:
            try:
                if 'Company_A' in entry.value:
                    print(entry.offset(row=1).value)
                    print(round(entry.offset(row=8).value/100,5))

             except (AttributeError, TypeError):
                 continue

edited Jul 4, 2018 at 8:56

answered Jul 3, 2018 at 8:49

Dror Av.

1,2446 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Peter Jung Over a year ago

I changed '==' to 'in', but I got an error says that argument of type 'cell' is not iterable.

Dror Av. Over a year ago

@PeterJung It's an error that occurs if the check is trying to check an empty cell (None), I've added a simple try and except, should do the trick, this is a quick and dirty fix though but for a simple thing it should work.

Collectives™ on Stack Overflow

how to find cell containing specific text using python?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related