Find word within cell & put value in adjacentSolved/Closed

Question

Hello,

I'd like to scan a column for a certain word (e.g. "apple") and put a code (e.g. "A") on the cell next to it each time it finds it. In this case, "apple" might be by itself or in the middle of a sentence.

System Configuration: Windows XP Internet Explorer 7.0

mubashir aziz · Accepted Answer

Hi, 

Suppose your data is from A1 to A100 or onward. Then in B1, copy this formula and drag it down:

=IF(ISERROR(SEARCH("*apple*",A1,1)),"","A ")

Hope, it will help!

Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran, Pakistan

panjneth · Answer

Hello Sir,

  I wanted to know about this thing in excel


single value in a cell lets say A1 is   45+45+65
now I wanted to set B1 cell a formula which will sum the values in this A1



Can anyone help me out???????

HEARDER · Answer

Hello I am just working in Excel with the same problem.  I have 40K of rows in column A and would like to look up a word(s) and have a value "Yes" or "No" or even true or false.  Does any one know how to save me time doing this?  

It would be greatly appreciated

David A · Answer

You could put in a formula in the new colium:

=if(isnumber(find("apple",RC[-1])),"yes","no")

If you want to later make those cells hard-coded text, simply COPY the cells and then PASTE SPECIAL and copy just the VALUE to put in the Yes or No as plain text.

dameeti · Answer

=IF(ISBLANK(RC[-1]),"", IF(OR( ISNUMBER(SEARCH("apple",RC[-1],1)), ISNUMBER(SEARCH("banana",RC[-1],1)), ISNUMBER(SEARCH("carrot",RC[-1],1)), ISNUMBER(SEARCH("donut",RC[-1],1)) ), "Yes","No"))

Two comments regarding the previous formula I provided: if the cell to the left is completely blank, then the result cell is also blank.  If you don't like that (and ONLY want yes/no, just remove the 1st part of the formula that checks for ISBLANK.

#1.  I did notice that most of the previous formulas preferred to check for ISERROR, but this is not the best solution, especially in a case asuch as this since ISERROR finds the case where it fails, whereas ISNUMBER finds the case where is succeeds, which is usually the case we're often looking for.

#2.  I used the preferred cell reference of RC vs. A1 format.  Using A1 format means you can never Copy/Paste the formula into the formula bar of another cell, but instead you are forced to drag down/over to copy it and have the relative cell references fixed by Excel.  (Why they switched from RC format to A1, I could never fathom.  It's so much harder to use and to read IMHO.)  If you want the formula in A1 format, merely change the RC[-1] references (which references the cell to the left regardless of it's location in the sheet) to the cell reference to the desired location.

For those who want to switch to RC format (or at least see what RC format is), check / change your preferences to R1C1 (I believe the General tab).

marl · Answer

Guys,
I tried the above formulas and its working great,but how about if there are more 7 word to find a a sentence..
My if fomulas only functioning up to seven times, meaning error if I put the 8th "IF"..

marl · Answer

I wish you could understand this..
I' d like to put in B2:B22 the name of the person that correspong to the color find in the phrases at
A2:A22. NOTE. C2:D11 is the lookup chart. Ex: BLUE = CRIS and WHITE = MARL. So B2=CHRIS & B3=MARL.
and what I want is an excel formula that will do that...ex. =if(search(keyword,A1),vlookup(.....),"error") or any formula that will make that possible..except using macro..many thanks.........


              A                                            B                        C                      D
1        PHRASES	                   ASSIGNED TO	KEYWORD	      ASSIGNED TO
2    THE OCEAN IS BLUE		                BLACK    	        ARNOLD
3     I LIKE HIS WHITE SHIRT		                BLUE	        CHRIS
4     BLUE IS MY FAVE COLOR			BROWN	        FERNANDO
5    THE OCEAN IS RED		                LAVENDER	        GENER
6    I LIKE HIS RED SHIRT		                MAGENTA          JEFF
7    RED IS MY FAVE COLOR			ORANGE	        JOHN
8   THE OCEAN IS YELLOW			PINK	         NAP
9    I LIKE HIS YELLOW SHIRT			RED	         PERRY
10  YELLOW IS MY FAVE COLOR			WHITE	         RAMIL
11  THE OCEAN IS PINK			YELLOW	        MARL
12  I LIKE HIS PINK SHIRT				
13  PINK IS MY FAVE COLOR				
14 THE OCEAN IS MAGENTA				
15  I LIKE HIS MAGENTA SHIRT				
16  MAGENTA IS MY FAVE COLOR				
17  THE OCEAN IS ORANGE	
18  I LIKE HIS ORANGE SHIRT	
19  ORANGE IS MY FAVE COLOR	
20 THE OCEAN IS BLACK	
21   I LIKE HIS YELLOW SHIRT	
22   BLACK IS MY FAVE COLOR

marl · Answer

GUys, I have another problem.
This is,hope understand.
I now how to print a specific cell to " print area setup", but here is...let say I have date in A1:D15, and I have also
a legend in F1: .telling which area you what to print..
SO when I write "1" in F1 in print the whole A1:D15, when I write "2" it will only print A1:B15, and when I write
"3" it will only print C1:D15,, thanks......

Jessica · Answer

Cent

CT · Answer

This discussion is badass.
Thank you everyone, you just helped me impress my boss :D

If you're ever in TO then I'll buy you a drink

Find word within cell & put value in adjacent

10 responses

Similar discussions

Subscribe To Our Newsletter!