# If cell contains partial text display value from other cell?

#### AlexBra

##### New Member
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.

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,"")

Is this what you need?

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

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)

Works great! Thanks.

Replies
4
Views
271
Replies
7
Views
868
Replies
7
Views
353
Replies
6
Views
400
Replies
9
Views
186

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.

### Which adblocker are you using?

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

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