Previous Topic Next Topic

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

Solved/Closed
shanej69 Posts 1 Registration date Sunday July 25, 2021 Status Member Last seen July 25, 2021 - Jul 25, 2021 at 06:17 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jul 26, 2021 at 11:37 AM
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.
Related:

1 response

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Jul 26, 2021 at 11:37 AM
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
0

Similar discussions

Formula for if cell contains word, assign value for multiple
hdiesel -
ac3mark -

1 response
Excel - IF function with multiple words
Nick -
user -

14 responses
If a cell contains any text then add a value
cbrizz -
Pharmanumbers -

20 responses
Find word within cell & put value in adjacent
Mr.G -
BogelBot -

39 responses
Copy data onto sheet only if cell has text
danipants -
checjb -

17 responses
How to convert a numeric value into English words in Excel 2007
pk -
BashirYazbik -

1 response