Find a particular word in a STRING of text ?

[Dolcevita]

Hello


First things first, I am so grateful to have access to a forum where I can ask questions on Excel?. I am a bit of an Excel virgin - I have been around the block many times but only ever got to first base, never really got into the saucy stuff - this is my first proper foray into the subject of functions, so if you could go slow with me I would be grateful!!

i am (trying to) creating an excel function. The cell, let's call it D5, contains a full sentence of words. I want to create a function that detects the word "work", or "job", or "furlough" in that sentence. If it finds any of those words within the sentence I want it to print the word "employment".

THe formula/function i have created only detects the word "work" if it's the only word in the cell.

Here is my wholly inadequate function:

=IF(K2="work","EMPLOYMENT") - its not doing what i want it to do!!!

My question is - where am I going wrong? Is the IF function the wrong function? Is there a way of Excel finding the word "work" in amongst the other words? If so could you help me construct it?

Thanks for the help guys!

Dolcevita

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B
1
2	I am not at work today	employed
3	I don't have a job	employed
4	Is the furlough scheme still running?	employed
5	I am unemployed
Data

Cell Formulas
Range	Formula
B2:B5	B2	=IF(SUM(IFERROR(FIND({"work","job","furlough"},A2),0))>0,"employed","")

 

[silentwolf]

Hi,

there are many ways but something like that would work

Function findEmployment(ByVal strText As String) As String
    If strText Like "*work*" Or strText Like "*job*" Or strText Like "*furlough*" Then
        findEmployment = "employent"
    Else
        findEmployment = strText
    End If
End Function

now you just could use the function within your vba code
HTH

 

[Dolcevita]

=IF(SUM(IFERROR(FIND({"work","job","furlough"},A2),0))>0,"employed","")

FLuff you are a genius, thanks a million!!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Dolcevita]

Hi,

there are many ways but something like that would work

Function findEmployment(ByVal strText As String) As String
    If strText Like "*work*" Or strText Like "*job*" Or strText Like "*furlough*" Then
        findEmployment = "employent"
    Else
        findEmployment = strText
    End If
End Function

now you just could use the function within your vba code
HTH

Hi SIlent wolf - thanks for this -but can I use thisprogram in Excel 2019 version? I have never seen an Excel function written like that before. Seems like programming language - would it work?

 

[silentwolf]

Hi,
This works in Excel 2019 in VBA..
But if you are not familiar with VBA then Fluffs answer ist working

Once you like to learn VBA you might find little UDF's User defined functions quite useful.

HTH

 

[jtakw]

Hi,

I'm thinking we should "Isolate" your key words in the text string, (e.g. the word "work" is inside "working", and "job" inside "jobless", etc.):

Book3.xlsx
	A	B
1	I am not at work today	Employment
2	I'm currently jobless
3	I don't have a job	Employment
4	Is the furlough scheme still running?	Employment
5	I am unemployed
6	I am no longer working
Sheet857

Cell Formulas
Range	Formula
B1:B6	B1	=IF(ISNUMBER(LOOKUP(2,1/SEARCH(" "&{"work","job","furlough"}&" "," "&A1&" "))),"Employment","")

 

[Dolcevita]

Hi,

I'm thinking we should "Isolate" your key words in the text string, (e.g. the word "work" is inside "working", and "job" inside "jobless", etc.):

Book3.xlsx
	A	B
1	I am not at work today	Employment
2	I'm currently jobless
3	I don't have a job	Employment
4	Is the furlough scheme still running?	Employment
5	I am unemployed
6	I am no longer working
Sheet857

Cell Formulas
Range	Formula
B1:B6	B1	=IF(ISNUMBER(LOOKUP(2,1/SEARCH(" "&{"work","job","furlough"}&" "," "&A1&" "))),"Employment","")

Thank you guys, really helpful..

 

[Fluff]

Glad we could help & thanks for the feedback.