Search : in
By :

Find word within cell & put value in adjacent

Last answer on Nov 18, 2009 4:51:22 pm GMT Mr.G, on Dec 17, 2008 3:52:40 pm GMT 
 Report this message to moderators

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.

Configuration: Windows XP
Internet Explorer 7.0

Best answers for « Find word within cell & put value in adjacent » in :
Vi/Vim - Finding a word Show[VI/VIM] Finding a word To find a word in VI / Vim, just type "/" or "?", followed by the word you’re searching for the search (downwards or upwards). Pressing the n key, will allow you to go directly to the next occurrence of the word....
[VBA] Detecting changes in cell Show[VBA] Detecting changes in cell The Event Change feature of a sheet will detects the change in the active cell but it gives no information about the content. The example given below will help you to find out if the cell was changed,...
Repainting a cell using excel VBA ShowRepainting a cell using excel VBA Issue Solution Note Issue I am writing a code in excel VBA to generate a report.I am totally new to VBA.Here's my code Private Sub CommandButton1_Click() Dim a1 As Integer Dim d1 As Integer Dim...
Download USA Unlisted Cell Phone Numbers ShowHas you friend flown for America without telling you where he/she would settle? Don’t panic! USA Unlisted Cell Phone Numbers is a program which can help you. By using this program, you simply need to know his/her phone number in order to find...
Download Dico Aero Show
Ask your question Reply

1

panjneth, on Mar 17, 2009 9:22:51 am GMT

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???????

   
Reply to panjneth

2

HEARDER, on Jun 18, 2009 1:34:28 am BST
  • +4

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

   
Reply to HEARDER

3

David A, on Jun 30, 2009 8:31:00 pm BST

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.

   
Reply to David A

4

mubashir aziz, on Jul 1, 2009 6:29:47 am BST
  • +3

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 ")




Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran, Pakistan

   
Reply to mubashir aziz

5

brad, on Sep 3, 2009 10:04:12 pm BST
  • +1

Hello,
the formula [=IF(OR(ISERROR(SEARCH("*apple*",a1,1)),"","A ")] works great, but what if I want to use multiple criteria with wild characters.
EG: *apple* = A
*orange* = B
*grapes* = C
if any thing other than apple, orange or grapes, it should show 'D'

Hope you would respond.

Thanks in advance

Configuration: Windows XP Internet Explorer 7.0

   
Reply to brad

7

mubashir aziz, on Sep 4, 2009 5:58:21 am BST
  • +1

Try it dude ... 

=IF(ISERROR(SEARCH("*apple*",A1,1)),IF(ISERROR(SEARCH("*orange*",A1,1)),IF(ISERROR(SEARCH("*grapes*",A1,1)),"","C"),"B"),"A")



Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran, Pakistan

   
Reply to mubashir aziz

8

 Jon, on Nov 18, 2009 4:51:22 pm GMT

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?

   
Reply to Jon

6

dameeti, on Sep 3, 2009 10:31:41 pm BST

=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).

   
Reply to dameeti
Ask your question Reply
They need your help