Previous Topic Next Topic

Formulas to search for certain words in cells

Solved/Closed
Juniore Guerra - Aug 20, 2010 at 12:15 AM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Apr 2, 2011 at 09:49 PM
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


Related:

2 replies

Answer 1 / 2
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Aug 20, 2010 at 06:15 AM
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
juniiore
Aug 20, 2010 at 02:28 PM
Thank you Venkat1926. It works. You will save me so much time each month.
0
CocoJo 67
Apr 2, 2011 at 05:35 PM
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 !!
0
Answer 2 / 2
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Apr 2, 2011 at 09:49 PM
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
0