Find words within a text string

[morz68]

Hi all, I have googled and not found what I need, so hoping you can help. I have a column of text, somewhere inside the text could be the word 'auth', I need a formula which will find it. Just within an If fx would be good. For instance if the cell contains the word 'auth', put the word Authenticate. This screen grab is not working, maybe I'm using the incorrect fx?

EFC Auth	=IF(ISTEXT(A1), "AUTH", "")
EFC Auth Brazil	=IF(ISTEXT(A2), "AUTH", "")
EFC Auth	=IF(ISTEXT(A3), "AUTH", "")
EFC Not	=IF(ISTEXT(A4), "AUTH", "")
EFC Not	=IF(ISTEXT(A5), "AUTH", "")
EFC Not	=IF(ISTEXT(A6), "AUTH", "")
EFC Auth	=IF(ISTEXT(A7), "AUTH", "")

 

[alansidman]

Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.Contains([Column1],"Auth") then "Authenticate" else null)
in
    #"Added Custom"

Column1	Custom
EFC Auth	Authenticate
EFC Auth Brazil	Authenticate
EFC Auth	Authenticate
EFC Not
EFC Not
EFC Not
EFC Auth	Authenticate

 

[morz68]

Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.Contains([Column1],"Auth") then "Authenticate" else null)
in
    #"Added Custom"

Column1	Custom
EFC Auth	Authenticate
EFC Auth Brazil	Authenticate
EFC Auth	Authenticate
EFC Not
EFC Not
EFC Not
EFC Auth	Authenticate

Thank you so much, bu I wanted a formula (old school) I haven't used PowerQuery, and I need to give the formula to a basic level user. Any ideas?

 

[AhoyNC]

Maybe.

Book3
	A	B
1	EFC Auth	Authenticate
2	EFC Auth Brazil	Authenticate
3	EFC Auth	Authenticate
4	EFC Not
5	EFC Not
6	EFC Not
7	EFC Auth	Authenticate
Sheet1

Cell Formulas
Range	Formula
B1:B7	B1	=IF(ISNUMBER(SEARCH("Auth",A1)),"Authenticate","")

 

[Phuoc]

Or try

=IF(Countif(A1,"*Auth*")>0,"Authenticate","")

 

[morz68]

Maybe.

Book3
	A	B
1	EFC Auth	Authenticate
2	EFC Auth Brazil	Authenticate
3	EFC Auth	Authenticate
4	EFC Not
5	EFC Not
6	EFC Not
7	EFC Auth	Authenticate
Sheet1

Cell Formulas
Range	Formula
B1:B7	B1	=IF(ISNUMBER(SEARCH("Auth",A1)),"Authenticate","")

Awesome, thanks!