Finding certain words in a cell and return a value based off the word found.

[Solved]
Report
Posts
1
Registration date
Sunday July 25, 2021
Status
Member
Last seen
July 25, 2021
-
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
-
Hello,
I need to find a certain word in a cell in a column and return text based on the value.
Example of this is if the word Apple is in a cell in then return A, but if Banana is in the next cell down in the column then return B.

1 reply

Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
Hi Shanj69,

Your example shows a pattern, column B shows the first letter of the text in column A. This can be achieved by using the following formula:
=LEFT(A2,1)
Drag the formula down for the rest of the cells.

If this is not on purpose, then you can use a nested IF formula like:
=IF(A2="Apple","A",IF(A2="Banana","B",""))

Or add to this code:
Sub RunMe()
For Each cell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
    If cell.Value = "Apple" Then cell.Offset(0, 1).Value = "A"
    If cell.Value = "Banana" Then cell.Offset(0, 1).Value = "B"
Next cell
End Sub


Best regards,
Trowa