Looking up text string and creating your own expression

[losamfr17]

Dear forum members,

I'm a beginner user, and I was wondering if you could please help me solve this problem...?

I'm looking for a way/function to look up a specific word in a text column, and I want to be able to call that cell something else. For example, I'm trying to look up the word "*major*" in column 31 (AE) and row 50, and if that word appears in that row and column, I want the cell to appear as "Major Member." Any suggestions?

Thank you in advance for your suggestions.

Best regards,
Losamfr17

 

[ProfessorPorcupine]

Try this (adjust as necessary):

Sub UpdateFoundCells():
'Updates cells found with a criteria with an new value (assumes column AE/ 21st and checks first 1,000 rows).


    Dim rngCurrentCell As Range
      
    For Each rngCurrentCell In ThisWorkbook.Sheets("Sheet1").Range(Cells(1, 31), Cells(1000, 31))
        If LCase(rngCurrentCell) = "major" Then
            rngCurrentCell = "Major Member"
        End If
    Next
    
End Sub

 

[losamfr17]

Professor Porcupine,

Thank you so much for sending me your code. I was just able to run it today. It gave me an error message (as you pointed out it needs adjustment). Since I have no background in VBA, I'm looking into a solution using online resources. I appreciate your time.

 

[63falcondude]

Have you tried using Find and Replace? Replace *major* with Major Member

 

[losamfr17]

Well my problem is a bit more complex. I'm trying to automate a task. Column G needs to be filled. Each row to be filled depends on a keyword, "major," written on column AE (31). For each row where "major" appears within the text string in AE, I want "Major Member" to appear in column G.

I hope this is clearer than my first post. Thank you for your consideration.

PS: if I were to use Find & Replace, could I automate that task for the entire data table?

 

[63falcondude]

If you have your data set up as a table, you can write this formula (note that I have my table headers as column 1 for the strings and column 2 where the formula goes.

=IF(ISNUMBER(SEARCH("major",[@Column1])),"Major Member","")

This will search for the word major in Column1 and if it finds it, show Major Member in column 2, otherwise it will be blank.

	A	B
1	Column1	Column2
2	I am a major pain	Major Member
3	123major456	Major Member
4	banana

<tbody>
</tbody>

When you add a new string into column 1, column 2 will automatically drag the formula down.

 

[losamfr17]

FalconDude,

Thank you so much! You're awesome. I wish I had your expertise.

You pretty much solved it, but I have one questions to add: instead of writing the column name, can I write the column letters (like "AE") number (31)?

 

[63falcondude]

Sure, replace [@Column1] with AE:AE

For my example, =IF(ISNUMBER(SEARCH("major",A:A)),"Major Member","") worked.

 

[losamfr17]

Also, why won't the IF function work alone?

Thank you!

 

[63falcondude]

Also, why won't the IF function work alone?

Thank you!

That I do not know. I first tried =IF(A2="*major*","Major Member","") to see if the wildcard * would pick major out of a string but it didn't work.