If a cell contains certain word.

jevi

Board Regular
Joined
Apr 13, 2010
Messages
219
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:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,530
Office Version
365
Platform
Windows
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,381
Office Version
365
Platform
Windows
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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,530
Office Version
365
Platform
Windows
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)
 

jevi

Board Regular
Joined
Apr 13, 2010
Messages
219
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,381
Office Version
365
Platform
Windows
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.
 

Forum statistics

Threads
1,084,749
Messages
5,379,617
Members
401,615
Latest member
syn_excel

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top