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)

 

[Scott Huish]

I wouldn't use ISERROR, but here is your macro without a loop and without selecting anything.

Sub ifforall()
With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .Formula = "=IF(ISERROR(SEARCH(""EDM"",A2)),""ANOTHER NUMBER"", ""EDM"")"
    .Value = .Value
End With
End Sub

 

[atown]

thanks for the help fellas- i will be experimenting with these and let you know

 

[hellebore08]

Yes you're right but I only posted what he wished that can be easily understood.

well, your code will serve him better..

cheers!

-e.rgabrieldoronila

 

[wcwbrennan]

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

Perfect! Just what I came looking for. Your formula ROCKS!!!

 

[Biz]

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

Nice formula. How would do this in vba using Search to get same results as your gem formula?

Biz

 

[nguyendk]

Can someone explain how this formula works? I am having trouble breaking this baby down. It's **** genius.

Nice formula. How would do this in vba using Search to get same results as your gem formula?

Biz

 

[James Snyder]

Concept only:

Dim return As Long
Dim value As String
With Range
    Select Case cell.value
        Case "EDM"
            ...code here
            value = cell.Value
        Case "EDM2"
            ...code here
            value = cell.Value
        Case Else
            return = CLng(cell.Value)
    End Select
End With

 

[Scott Huish]

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

OK.

The first part REPT("z",255) will return the last text value from the array.
The {1,2} in the CHOOSE Formula will return 2 values to an array.
The first array contains "Another Value" and the second array contains all the results from the FIND statement which will be either #N/A or the value from E1:E10 (The Result Vector). If all the values return #N/A, then the only other Text Value is "ANOTHER VALUE" so this is what it will return.
The second lookup uses 1000 (a number greater than would possibly be returned from this problem, so will therefore return the last match from E1:E10 to the 2nd value of the array. We made "ANOTHER VALUE" first, so that if there was a 2nd value it would return this one instead.

 

[edzimmer]

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

Sheet1

*	A	B	C	D	E
1234 EDM100	*	EDM	*	EDM
5678 ADP2	*	ADP	*	ADP
*	*	ANOTHER VALUE	*	VAL3
512 XQ5	*	ANOTHER VALUE	*	VAL4
*	*	*	*	VAL5
*	*	*	*	VAL6
*	*	*	*	VAL7
*	*	*	*	VAL8
*	*	*	*	VAL9
*	*	*	*	VAL10

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C1 =LOOKUP(REPT("z",255),CHOOSE({1,2},"ANOTHER VALUE",LOOKUP(1000,FIND($E$1:$E$10,A1),$E$1:$E$10))) C2 =LOOKUP(REPT("z",255),CHOOSE({1,2},"ANOTHER VALUE",LOOKUP(1000,FIND($E$1:$E$10,A2),$E$1:$E$10))) C3 =LOOKUP(REPT("z",255),CHOOSE({1,2},"ANOTHER VALUE",LOOKUP(1000,FIND($E$1:$E$10,A3),$E$1:$E$10))) C4 =LOOKUP(REPT("z",255),CHOOSE({1,2},"ANOTHER VALUE",LOOKUP(1000,FIND($E$1:$E$10,A4),$E$1:$E$10))) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Would you mind explaining how this formula works? It does work great, I would just like to understand how?

 

[Scott Huish]

Look at the post right above yours. I explained it in May