IF statement w/ find text w/in string

[atown]

Hey,

wondering if anyone can solve this for me- havent been able to find a really good solution to this answer yet. I am trying to utilize an IF statement that will find a value within a text string and if the value exists, then return that value, else return another number.
A1:
23994 AD EDM1

using the above example, if A1 contains the value "EDM" then return "EDM" else return (another value)

 

[njimack]

=IF(ISNUMBER(SEARCH("EDM",A1)),"EDM","another value")

 

[Scott Huish]

=if(isnumber(find("EDM",a1)),"edm","something else")

 

[Desu Nota from Columbus]

Hey,

wondering if anyone can solve this for me- havent been able to find a really good solution to this answer yet. I am trying to utilize an IF statement that will find a value within a text string and if the value exists, then return that value, else return another number.
A1:
23994 AD EDM1

using the above example, if A1 contains the value "EDM" then return "EDM" else return (another value)

=IF(ISNUMBER(SEARCH("EDM",A1)),"EDM","Another Number")

 

[atown]

thx a lot- if i had to do this search for multiple values within a column of data, i.e. look for like 10 different values and return that particular value if contained in a cell- is there an easier way to do that then make a rediculously long if/search statement?

 

[Desu Nota from Columbus]

Yes, you would need to use a macro--which will take me an hour or so to write and troubleshoot... I'm in the learning process of writing code so hopefully an ExcelMVP can answer you.

 

[atown]

ah ok i was thinking macro also but wasn't sure if there was a way to do a vlookup or a index/match function without using vba

 

[Scott Huish]

Why would you need a macro?

Assuming your list in E1:E10

=LOOKUP(REPT("z",255),CHOOSE({1,2},"ANOTHER VALUE",LOOKUP(1000,FIND($E$1:$E$10,A1),$E$1:$E$10)))

Excel Workbook
	A	B	C	D	E
1	1234 EDM100		EDM		EDM
2	5678 ADP2		ADP		ADP
3			ANOTHER VALUE		VAL3
4	512 XQ5		ANOTHER VALUE		VAL4
5					VAL5
6					VAL6
7					VAL7
8					VAL8
9					VAL9
10					VAL10
Sheet1

 

[hellebore08]

This is the formula

=IF(ISERROR(SEARCH("EDM",AG3827)),"ANOTHER NUMBER", "EDM")

and if you're looking for a macro, please try this and let me know if it works..

Assuming your data starts at Cell A2

Create a module and copy the code below.

Sub ifforall()
Range("A2").Select
Do Until IsEmpty(ActiveCell.Value)
ActiveCell.Offset(0, 1).FormulaR1C1 = "=IF(ISERROR(SEARCH(""EDM"",RC[-1])),""ANOTHER NUMBER"", ""EDM"")"
ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(0, 1).Value
ActiveCell.Offset(1, 0).Select
Loop
End Sub

It will look for cells with values under column a (starting from cell A2), write the formula beside them, and rewrite them as values. It will loop until the next line (of course, going down) is empty.

cheers!

-e.rgabrieldoronila

 

[Scott Huish]

The op is looking for a whole range of values in each cell. But your macro could be done without a loop, and you rarely need to select cells in order to work with them. You can work with ranges directly in VBA.