If cell contains partial text display value from other cell?

AlexBra

New Member
Joined
Dec 15, 2016
Messages
2
Hello,

Hello,

I am trying to sort through some bank records. I am trying to pull transaction amounts from cell G into cell H if cell E contains partial text (not case specific). For example if cell E3 contains "MUGSY" then display "-17.25" in cell H3. It must search for partial text because some of the transactions I have contain the last 4 of the card that was used (See E5/6/7) and I want to pull both of those values into the same column regardless of which card was used.

It would also be helpful if I could pull the values from G if column E contained either "MH CAFE" or "INTEL OC" into the same column.

Im trying to figure out how much my wife and I are spending when we eat out for lunch at work.

Any help would be greatly appreciated.



excel_help.jpg



Thanks,
Alex
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,108
Place this formula in cell H2 and copy it down as far as you need it: =IF(OR(COUNTIF(E2,"*MUGSY*"),COUNTIF(E2,"*MH CAFE*"),COUNTIF(E2,"*INTEL OC*")),G2,"")
 

István Hirsch

Well-known Member
Joined
May 16, 2013
Messages
1,634
Is this what you need?

=IF(COUNTIF(E2,"*MUGSY*")+COUNTIF(E2,"*INTEL OC*")+COUNTIF(E2,"*MH CAFE*"),G2,"")
 

Declamatory

Active Member
Joined
Nov 6, 2014
Messages
310
If Mugsy was just an example and can change then you can create a search cell e.g. I2.

=IF(OR(ISNUMBER(SEARCH("MUGSY",E2)),ISNUMBER(SEARCH("MH CAFE",E2)),ISNUMBER(SEARCH("INTEL OC6",E2))),G2,0) (THIS LOOKS SPECIFICALLY FOR MUGSY, MH CAFE AND INTEL OC6)

=IF(OR(ISNUMBER(SEARCH($I$2,E2)),ISNUMBER(SEARCH("MH CAFE",E2)),ISNUMBER(SEARCH("INTEL OC6",E2))),G2,0) (THIS LOOKS FOR MH CAFE, INTEL OC6 AND WHATEVER IS TYPED INTO CELL I2)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,314
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top