Ask a question Report

Find word within cell & put value in adjacent [Solved/Closed]

Mr.G - Latest answer on Mar 26, 2015 02:38AM
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.
Read more 
Answer
+87
moins plus
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 ")





Jon- Nov 18, 2009 11:51AM
Same method above, but is it possible to do this using VBA? I have too many Groups for the formula bar.


Put

Apple 1
Apple 2
Apple 3
---------------
into Group 1


Orange 1
Orange 2
Orange 3
---------------
into Group 2

Berries 1
Berries 2
Berries 3
---------------
into Group 3

Lastly, what if Apples contain characters/symbols such as %$@!(), how do you make VBA treat these symbols as text?
taf- Mar 21, 2010 09:05AM
This is very nice it solved my problem
SK- Aug 10, 2010 04:09AM
SEARCH PARTICULAR VALUE FROM 3 CELLS CONTAINNG FALSE VALUE IN CELLS
SK- Aug 10, 2010 04:14AM
I HAVE 3 CELL E2 F2 & G2 , VALUE IN E2 '20D' , IN F2 'FALSE' AND IN G2 'FALSE', I WANT TO CHOOSE THE RIGHT VALUE i.e. 20D in another cell from these 3 cells
Buck- Aug 27, 2010 10:56PM
Man, you are good!!!!
I searched many places for this solution but all failed.
However, anthoer easy way if use autofilter and set criteria to customised and on the drop down list select " Contain"
Answer
+11
moins plus
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- Feb 18, 2010 12:14AM
THanks guys,,Its working,,,yes....but what is the funtion of 9999 at the formula...(a dummy)
rizvisa1 4300Posts Thursday January 28, 2010Registration date ContributorStatus December 6, 2014 Last seen - Feb 18, 2010 03:03AM
The lookup is

LOOKUP(lookup_value, lookup_vector, result_vector)

9999 is just a random long number than the length of your string. 30 would have worked fine too. Just wrote 9999 for no reason
mark- Apr 21, 2010 09:06PM
This is absolutely fantastic!! Thanks so much - you have saved me much work (as well as my bacon!!) :-)
mike_c- Aug 12, 2010 12:52PM
how did you get this to work, all I see is a 0 for each line.
rizvisa1 4300Posts Thursday January 28, 2010Registration date ContributorStatus December 6, 2014 Last seen - Aug 17, 2010 09:53PM
How are yo using it Mike_c ?
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like http://www.speedyshare.com/ , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
Answer
+9
moins plus
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

Answer
+5
moins plus
=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).

Answer
+2
moins plus
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"..

rizvisa1 4300Posts Thursday January 28, 2010Registration date ContributorStatus December 6, 2014 Last seen - Feb 17, 2010 09:00AM
and your answer is....
Let say name and color assignment is on sheet COLOR
then you can use this formula to do the lookup

=LOOKUP(9999,SEARCH(color!A$2:A$11,A2),color!B$2:B$11)
mubashir aziz 191Posts Sunday April 12, 2009Registration date February 16, 2010 Last seen - Feb 16, 2010 04:14AM
Although we have only 7 nested if's but you can use more then 35 Ifs by using this link ... I"ve attached a file over there so you can see that file as well ....


http://www.excelforum.com/excel-general/685394-shorten-nested-formula.html






marl- Feb 16, 2010 04:36AM
Thanks, i will try it..
FOr now this the problem sample i need to solve.If you can help me..please..
I have phrases and a lookup table.I want to put the name of personcorresponding to the color
found on the phrases.

1.Phrases is on Cell A1 up to A12...
2.assigned to is on CEll B1 up to B12 - (where i want to put the answer)
3.(lookup table) is on CELL D1:E1 up to D11:E11



---phares------ --- assigned to----
THE OCEAN IS BLACK
I LIKE HIS BLUE SHIRT
BLUE IS MY FAVE COLOR
THE OCEAN IS RED
I LIKE HIS RED SHIRT
RED IS MY FAVE COLOR
THE OCEAN IS YELLOW
I LIKE HIS YELLOW SHIRT
YELLOW IS MY FAVE COLOR
THE OCEAN IS PINK
I LIKE HIS PINK SHIRT
PINK IS MY FAVE COLOR
THE OCEAN IS MAGENTA
I LIKE HIS MAGENTA SHIRT
MAGENTA IS MY FAVE COLOR
THE OCEAN IS ORANGE
I LIKE HIS ORANGE SHIRT
ORANGE IS MY FAVE COLOR
THE OCEAN IS BLACK
I LIKE HIS YELLOW SHIRT
BLACK IS MY FAVE COLOR


(lookup table)
KEYWORD ASSIGNED TO
BLACK ARNOLD
BLUE CHRIS
BROWN FERNANDO
LAVENDER GENER
MAGENTA JEFF
ORANGE JOHN
PINK NAP
RED PERRY
WHITE RAMIL
YELLOW Marl
mubashir aziz 191Posts Sunday April 12, 2009Registration date February 16, 2010 Last seen - Feb 16, 2010 05:05AM
Sorry i couldn't get you ....... please be more specific like


Column A1:A12
THE OCEAN IS BLACK
I LIKE HIS BLUE SHIRT
BLUE IS MY FAVE COLOR
THE OCEAN IS RED
I LIKE HIS RED SHIRT
RED IS MY FAVE COLOR
THE OCEAN IS YELLOW
I LIKE HIS YELLOW SHIRT
YELLOW IS MY FAVE COLOR
THE OCEAN IS PINK
I LIKE HIS PINK SHIRT
PINK IS MY FAVE COLOR

Column B1:B12
.VALUES
.
.
.


Column D11:D12


zmarlz04 10Posts Tuesday February 16, 2010Registration date February 24, 2010 Last seen - Feb 16, 2010 10:50AM
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.........
(IF YOU CAN UNDERSTND..PLS GIVE ME YOUR EMAIL..I WILL SEND YOU AN EXAMPLE EXCEL SHEET.TNX VERY MUCH

A1:A22
1 PHRASES
2 THE OCEAN IS BLUE
3 I LIKE HIS WHITE SHIRT
4 BLUE IS MY FAVE COLOR
5 THE OCEAN IS RED
6 I LIKE HIS RED SHIRT
7 RED IS MY FAVE COLOR
8 THE OCEAN IS YELLOW
9 I LIKE HIS YELLOW SHIRT
10 YELLOW IS MY FAVE COLOR
11 THE OCEAN IS PINK
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

ANSWER SHOULD BE ON ADJACENT CELL : B2:B22


This is the lookup table: colors are in cell C2:C11 and the assigned persons is on cell D2:D11
BROWN- FERNANDO
LAVENDER- GENER
ORANGE - JOHN
PINK - NAP
RED -PERRY
WHITE -RAMIL
YELLOW - MARL
BLUE-CHRIS
Answer
+0
moins plus
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???????

Answer
+0
moins plus
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.

Answer
+0
moins plus
Answer
+0
moins plus
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

Answer
-1
moins plus
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......

rizvisa1 4300Posts Thursday January 28, 2010Registration date ContributorStatus December 6, 2014 Last seen - Feb 18, 2010 02:59AM
perhaps you should put this question as a new one question. This thread is turning into forum itself.
marl- Feb 18, 2010 06:53AM
ah ok.thanks,,but anyway i'd found the solution through conditional formatting...works great....
syrus- Mar 26, 2015 02:38AM
hide the row or cols that you dont need
This document entitled « Find word within cell & put value in adjacent » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.

Not a member yet?

sign-up, it takes less than a minute and it's free!

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.