Search cells in excel for string

Question

So I have a project I'm working on where I have a large excel spreadsheet and I want to search one of the columns/cells and if the cell contains a certain word then I want to add a tag in another column but the same row.

So the cell would have a long description in it and if the description contained a keyword I'm looking for it would add a tag in another column in the same row. I would like to able to search for more than one keyword at a time.

I've messed around with openpyxl for awhile but that hasn't really panned out. I am familiar with python, C, C++, and Java so if you could help me with any of those languages I would really appreciate it. Python is preferred.

Can you provide some more details on what you've already tried? — Jonny
– Jonny, Commented Jun 22, 2017 at 18:21
Check out the pandas library in Python. It has some useful functions to help search through a dataframe. You can also read in excel sheets as dataframes. — Peter
– Peter, Commented Jun 22, 2017 at 18:22

Gary's Student · Accepted Answer · 2017-06-22 18:30:42Z

1

Say we want to search column A for gold. This macro:

Sub FindGold()
    Dim r As Range
    For Each r In Intersect(ActiveSheet.UsedRange, Range("A:A"))
        If InStr(1, r.Value, "gold") > 0 Then r.Offset(0, 1).Value = "found it"
    Next r
End Sub

can produce:

Adjust the code to meet your schema.

answered Jun 22, 2017 at 18:30

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

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

Comments

Pornthep Nivatyakul · Accepted Answer · 2022-10-17 02:36:03Z

1

import numpy as np
import pandas as pd

# open excel file
discount = pd.read_excel("C:\\data\\discount.xlsx", sheet_name="ALL0822")

# search string in cell "Discount Total"
row, col = np.where(discount == "Discount Total")
print(row, col)

# get value from cell
cash = discount.iloc[row,col+2].values[0] 
print(cash)

edited Oct 17, 2022 at 2:36

answered Oct 17, 2022 at 2:34

Pornthep Nivatyakul

393 bronze badges

Collectives™ on Stack Overflow

Search cells in excel for string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related