CODEWITHSUNDEEP
excelvba
DZIQ
DZIQ

Reputation: 1

How to search for specific word or words in a column of data and return that value

I would like my macro to search a column of data for specific words. If these words appear in the data, I would like to return these values to another cell. If not I would like it to return "None". Below are the results I would expect.

Search column of data for word "Restricted" and "Unrestricted". If only one of these words appear in the data, return that word as a value (Ex. If only "Restricted" is in the data, return "Restricted". If both words appear in the data, return "Restricted/Unrestricted"). If neither of the words appear in the data, return "None".

Upvotes: 0

Views: 87

Answers (2)

Pᴇʜ
Pᴇʜ

Reputation: 57683

No need of VBA here. Use the COUNTIF function. To count the Restricted and Unresticted in a column.

=IF(AND(COUNTIF(A:A,"Restricted")>0,COUNTIF(A:A,"Unrestricted")>0),"Restricted/Unrestricted",IF(COUNTIF(A:A,"Restricted")>0,"Restricted",IF(COUNTIF(A:A,"Unrestricted")>0,"Unrestricted","None")))

This formula checks column A.

Upvotes: 1

Bálint Kolosi
Bálint Kolosi

Reputation: 83

You have pay attention to the fact that the word Unrestricted contains the word Restricted as well if you compare them case-insensitive.

You can use Instr function with vbBinaryCompare (case-sensitive) as its last argument to see if a string appears in another string.

StrText = cell.Value

If InStr(1, StrText, "Unrestricted", vbBinaryCompare) > 0 And InStr(1, StrText, "Restricted", vbBinaryCompare) > 0 Then
    StrResult = "Restricted/Unrestricted"
ElseIf InStr(1, StrText, "Unrestricted", vbBinaryCompare) > 0 And InStr(1, StrText, "Restricted", vbBinaryCompare) = 0 Then
    StrResult = "Unrestricted"
ElseIf InStr(1, StrText, "Unrestricted", vbBinaryCompare) = 0 And InStr(1, StrText, "Restricted", vbBinaryCompare) > 0 Then
    StrResult = "Restricted"
Else
    StrResult = "None"
End Sub

Upvotes: 0

Related Questions