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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
11,727
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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
319
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)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,423
Messages
5,837,152
Members
430,479
Latest member
mamush200

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