Previous Topic Next Topic

Formulas to search for certain words in cells

[Solved/Closed]
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 -
Hello,

I have monthly bank statements that are downloaded into excel, over 2500 line items.

In Column A, I have the description of the transactions (all 2500 of them), on column B I have the dollar amount of the transactions in column A.

I need to look for all transactions that contain the words "returned item" as part of the entire description. The description will usually list "returned item john doe", 10 or 20 lines below, there will be another one that reads "returned item jane smith" and so forth. There might be like 250 of them and I have to manually go thru all 2500 line items and copy the dolalr amount into column C (just for those items with the partial description of returned items.

I need to find a formula to place on column C. I want it to search for the words "returned items" which will be part of the description on column A and to bring the dollar value on column B over to column C, but only if Column A has the words "returned item" as part of the entire description. Thank you.

Junior Guerra


2 replies

Answer 1 / 2
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 802
SAMPLE DATA FROM A1 TI B11

HEADING 1 H2
AAAAAAAAAAAAAA 1
RETURNED ON AAA 2
SSSSSSSSSSSSSSS 3
DDDDDDDDDDDDDD 4
FFFFFFFFFFFFF 5
RETURNED ON BBB 6
GGGGGGGGGGGGGGG 7
HHHHHHHHHHHH 8
JJJJJJJJJJJJJJJJ 9
RETURNED ON CCCC 8


IN c2 copy this formula
=IF(LEFT(A2,11)="RETURNED ON",B2,"")
copy C2 down.
1
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Report

Thank you Venkat1926. It works. You will save me so much time each month.
Report

hi - what if i'm not looking for just "returned items" but a list of 250 words to match against the same 25000 words?? how can i use an array function with the same result? thanks !!
Answer 2 / 2
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 802
1. Length of cell contents (text) 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.

2. if those 250 characters are together on the left use the same formula changing 11 to 250. try this. I have never done this
Ask a question