If a cell contains certain word.

jevi

Active Member
Joined
Apr 13, 2010
Messages
339
Office Version
  1. 2016
Platform
  1. Windows
Hi All,

I'm trying to do this formul but is not working.

=IF(ISNUMBER(SEARCH("*LR 10*";A2));"M01";B2)

so in column A2 I have the long desription of the loan and I want to find the ones that contain "LR 10", and if this is true to put in cell C2 "M01" otherwise the value that is in cell B2. But is not giving the result I want.

So I need help...thank you:)
 
Last edited:

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Hmm, seems to work for me. In what way is it "not giving the result I want"?
Some sample data maybe?

Also, whilst they won't do any harm, you don't need the asterisks in that formula.

Note that my formula delimiter is "," not ";" like yours.

Excel Workbook
ABCD
2Some long description including LR 10 and other textxxxM01M01
3Some long description including LR 11 and other textyyyyyyyyy
SEARCH
 
Last edited:
Upvote 0
If the data in col A has come from a website or some other 3rd party software, the spaces maybe non-breaking spaces, in which case try
=IF(ISNUMBER(SEARCH("LR?10",A2)),"M01",B2)
Although this will pick-up any instance where you have LR & 10 separated by a single character.
 
Upvote 0
If the data in col A has come from a website or some other 3rd party software, the spaces maybe non-breaking spaces,...
Good point, and we could target that &/or space specifically with

=IF(ISNUMBER(SEARCH("LR 10",SUBSTITUTE(A2,CHAR(160)," "))),"M01",B2)
 
Upvote 0
Thank you to all of you for the answers. Now that I did the test from the home computer is working my formula but because I wrote the test LR 10 in the excel file while at office is not working. The test that i have in the office is in txt extracted from another program so I will use "?" and the other formula. I will try on monday and let you know if it is working, but I guess was not working as Pluff said as it is a text from another program.

Have a nice weekend all of you.
 
Upvote 0
If you are likely to something like LRP10 in the cell and it should return B2 rather than "M01", then you are better off using Peter's code.
 
Upvote 0

Forum statistics

Threads
1,223,162
Messages
6,170,432
Members
452,326
Latest member
johnshaji

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