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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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,"")
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,214,684
Messages
6,120,875
Members
448,993
Latest member
InquisitiveFrog

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
Back
Top