Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Formula To Search For A Word In Specified Cells


I am trying to figure out a formula which will search the contents of 6 cells (o2, p2, q2,r2,s2,t2) for the word "sugar". If it is found in either of these cells, I would like the formula to result in "true" or "yes", if not, "false" or"no" would be the result.

Cells (o2, p2, q2,r2,s2,t2) contain text which can include the word "sugar" but will never contain only the word sugar.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Search For A ”Word” In Cells
Is possible to make a script that search for a ”Word” in cells with more than one word?

View Replies!   View Related
Sumproduct Formula To Search For A Particular Word
Is it possible to use the Sumproduct formula to search for a particular word
and grab the amount in the next column, but 1 row down? For example, I would
search for region 1, but want to grab the figure in the total loans column
next to Consumer.

Region Loan Type Total Loans
--------- ------------ --------------
Region 1 Commercial 150000
Consumer 75000
Region 2 Commercial 90000
Consumer 145000



View Replies!   View Related
How To Search For A Word In A Column And Have Entire Row Opaque Once Word Is Found
I need my macros to search for the word "Cancel" or "Cancelled" in columns "T" and "U". Once found, I need the macros to make that entire row an opaque shading.

There will be other wording in these cells that contain "Cancel" or "Cancelled". Is it possible for the macros to search in the sentence and find the words "Cancel" or "Cancelled"

I started on the code below but am stuck.

View Replies!   View Related
VBA: Search Function: Which Shows An Input Box Where You Can Enter A Word To Search For
I'm from Bavaria, Germany. Right now, I am doing an internship for my studies. my problem: I need a search procedure which shows an Input Box where you can enter a word to search for. It should work like the original Excel search (Ctrl-F), but with a simpler design, like with my own Text "Enter your Query" and a Button "Submit Query" / "Quit search". Is there the possibility to Highlight the Search Target? The problem hereby is that this highlighting should not be permanent. That means the user sees the target for which he searched for, the cell highlighted in a different color, etc. But as soon as the user clicks onto another cell, etc., the highlighting vanishes. If there is no fitting match, there should be a MessageBox like "Sorry the Target xyz cannot be found"

View Replies!   View Related
Formula To Calculate How Many Cells Show A Specific Word
How can I create a formula to add up how many cells in a column show a particular set of text? For instance; the column in my spreadsheet looks something like this:

MJR
DML
MJR
RJG
RJG
MJR
DML

I want to know how I can create a formula that will automatically tell me how many times "MJR" is listed; how many times "DML" is listed, and so on.

View Replies!   View Related
Formula Request To Search For Blank Cells
I am looking for a formula that will search a range on multiple worksheets for the FIRST blank cell and then make that cell the current active cell.

View Replies!   View Related
Search Word Within Cell
I have a set of data which contains " Account Heads" in Column "A" and "Data" in Column "B", "Data" contains "Account Heads" with some other text as well, i want to have the Account Head in Column "C" based on the "Account Heads" from "Data"

View Replies!   View Related
Partial Word Search
Is there a command out there that i can use to search a cell or textbox for part of a word, eg. my word being phone, and for it to look at cellphone and recognize it.

View Replies!   View Related
Search For Text That Does Not Contain A Certain Word
I'm looking for a macro to search for records that does not contain the word hello world anywhere(any column & any row) on a spread sheet. Then I want to delete that record. I prefer not to use the autofilter function, if possible.

View Replies!   View Related
Search For A Word In Specific Columns
I am trying to create the ability to search for a word in specific columns. In my case below I want to search D5:E500 but it's not working. It searches the whole ppage.

Private Sub searchfind_Click()
Dim searches As String
searches = searchfirstname & searchlastname
If WorksheetFunction.CountIf(Range("D5:E500"), searches) = 0 Then
Exit Sub
End If
Cells.Find(What:=searches, After:=ActiveCell, SearchOrder:=xlByRows, SearchDirection:=xlDown, _
MatchCase:=False, SearchFormat:=False).Activate
End Sub

View Replies!   View Related
Search For Word In A String Of Data
I have put a formula in excel to count how many times the word 'administration' appears in a column:

=COUNTIF(K2:K99,"Administration")

Unfortunately, the output that I am searching has mulitple words in it, separated with a colon and no space. My formula skips the count if the word Administration is not completely on it's own

e.g. Administration counts 1
Administration;Cardiology does not count

View Replies!   View Related
Search A Cell For Uppercase Word Only
This is wrekcing my head as I'm sure it can be done with an Array formula and I cant seem to get it sorted -

Say I have 5 Rows of text starting in A2
"A quick word"
"A quick WORD"
"A"
"word"
"WORD"

I need an excel formula to say "False", "False", "False", "False", "TRUE".

All conditions to be met for TRUE are - Word must in uppercase, Uppercase Word must be longer than 1 character, Sentence (i.e. cell) can not contain a space. This is the path I've gone down, but almost certainly incorrect -

=IF( FIND(CHAR(64+COLUMN($A$1:$Z1)),$A$2,1),TRUE,FALSE)

[CTRL+SHIFT+ENTER]

View Replies!   View Related
Search A Range Of Words, That Have 2 Or More Letters Of One Word
I have a key-word, and I need to search a 90,000 words in a range where i should find several words that have 2, 3 or 4 letters of the first letter of the key-word, then find several words that have 2, 3 or 4 letters of the second letter of the key-word,


View Replies!   View Related
VLOOKUP - Search Only One Word From Long Text, Write
Attached is the mock excel spreadsheet. I want to read "sam" from the lookup column's long text "sam is good" and then write "4" in the next column. Similarly read "white" from the long text "white is tired" and write "1". And so on,,,,, For more criteria, see box highlighted in yellow,,,,I used VLOOKUP but what am able to make it work only when there is one word "sam" in the lookup column. It returns #NA when the text is "sam is good". It should write nothing if none of the criteria is met and should keep doing until the last cell in the lookup column.

View Replies!   View Related
Remove Commas From All Cells, Search & Replace Error: Formula Is Too Long
I have a large spreadsheet, within which i am trying to remove commas from all cells. I get the error 'formula is too long' when I carry out the search. Some of the cells are >1024 characters in length and contain dates, text etc.

View Replies!   View Related
Using The =IF(ISNUMBER(SEARCH Function To Search Multiple Cells)
I' having trouble using the =IF(ISNUMBER(SEARCH formula to search multiple cells.

I can get it to work to search one cell (as below):

=IF(ISNUMBER(SEARCH("Same",G10)),"No Change",'Aug 09 Matrix'!F10)

(In this case the respone is No Change as Cell G10 contains "Same")

However cannot get it to work for several cells. I have pasted below and highlighted the function I would like it to perform:

=IF(ISNUMBER(SEARCH("Same",G10:R10)),"No Change",'Aug 09 Matrix'!F10)

Essentially the other cells in that row (G10:R10) all contain "Same", however for some reason it is identifying this as FALSE and putting in the data from the 'Aug 09 Matrix' sheet.

View Replies!   View Related
Opening Word & Count Word Instances In A Word Document
I have an excel program that is supposed to count word instances in a word document. I can't seem to find the right declaration for a word document.
For example to declare a workbook in excel its

Dim wb As Work Book

I've tried

Dim doc As Word.Document
'or
Dim doc As Word.Application

as shown in some of the forum posts, but an error user-type not defined keeps displaying.

View Replies!   View Related
Search Word For Text And Send Values Next To Text To Excel File
I am using Windows XP and Office 2003.

I am looking to automate a process where information is sent to someone in a text format and they in turn transfer that data to an excel file. It basically looks like this (but includes a lot more info):

Service Request #: 123456
Instrument Type: New Instrument
Lot/Serial #: 123456
SR Type: Product Complaint
Service Coverage: Maintenance Agreement

The info on the left (text preceding the colon) is always the same but the values following the colon can change. The excel file has all of the text before the colon and they just enter the information into the cells. I am looking to make it so that an Excel macro (or maybe a word/outlook macro) automatically scans the text document and sends this info to the exact same cells in excel every time.

The text file is actually a message from an Oracle database and I am not going to be able to make any changes to the way the data comes in.


View Replies!   View Related
Search CSV Files For Word & Make .xls Files Of Them
I have 22 CSV files. I want to search each of those files for a specific text string (ie: "profile" or "description"). When the macro finds the word in any one of the CSV files I want it to copy that complete csv worksheet and paste it into an excel xls file.

how I can go about doing this. Not also that while vlookup is possible, its very time consuming if i have to do this 50 times over and each time the csv files have different file names. that is why i want to search within a file (each file has its own unique identifying text string)

View Replies!   View Related
IF.. Contains...then: Search Through The Text String In That Cell And Find A Certain Word, Find And Retrn A Value
I am having trouble getting my IF statement to test if the cell contains the text "sale" return "X" if not "Y". I need it to search through the text string in that cell and find a certain word, and if it finds that word, retrn a value. I am really having difficulty with is what symbol or function do I use for the logical test? (i.e. =, <>, MATCH, INDEX?)

View Replies!   View Related
Insert Word After Formula
I have a simple AVG formula and I would like to insert the word "minutes" after the result.
I cant get it to work .... i know this is simple, but I am drawing a blank!

AVERAGEIF('producton 3'!E:E,"march 2008",'producton 3'!G:G)

View Replies!   View Related
Formula Written In Word
I wrote out a long IF statement in Word, but when I pasted in the formula bar, I got an error. I've determined that the quote marks are causing the error. Can anyone advice how to fix this going through and changing the quote marks one-by-one?

View Replies!   View Related
UDF If Formula That Contains Specific Word
I have a UDF which basically accomplishes a lookup. To do that I use this
Function name(number As String)
Select Case number
Case Is = "1"
name = "BOB"
.
.
.

View Replies!   View Related
Added Up # Of Cells Containing A Specified Word
In cell A1 I would like to have a formula that indicates the number of times the word "Yellow Pages" appears in cells C10:C1000. The C column inidicates a referral source. So some of the cells will have "Yellow Pages" and some wont. In cell A1 I would like to have it tell me the total # of referrals from the lead source "Yellow Pages".

View Replies!   View Related
Grab First Word In Cells
I would like to automate this process that I'm doing currently through the "Text to Columns" function.

Basically I have various cells that contain many worlds such as:

FRESH LUNETTE SOLEIL REES 1'S
INFOKUS SOLEIL LECTURE LUNETTES 1'S
KENSIE LUNETTE SOLEIL ELEORA 1'S
KENSIE LUNETTE SOLEIL ELIZABETH 1'S
KENSIE LUNETTE SOLEIL JAMES 1'S
KENSIE LUNETTE SOLEIL VESPER 1'S
KIDS LUNETTE SOLEIL HOUSTEN 1'S
KIDS LUNETTE SOLEIL MINNIE 1'S

My goal is to separate the first world from the rest of the words in one cell.

Since all of the words are seperated by space, I use the the "Text to Columns" function and separate the words, so that I can get the first word into one column. The rest of the words have to be merged again through "Concatenate".

What I'm looking for is a VBA code or Excel function that can identify the first world (which vary in character lengths) and put it into one column.

View Replies!   View Related
Word Count In Specific Cells
I am trying to find a formula that will give me a word count in specific cells.

View Replies!   View Related
Text Split For The Name Only In Cells Where Contains The Word Name
I would like to do a text split for the name only in cells where contains the word "Name". For example: Name A&A Enterprize

Text split function for it is =MID(A1,FIND(" ",A1)+1,255)
so it only recognizes A&A Enterprize.

How would I add an IF, Then function (If the any cell contains the word Name, apply the text split function, for cells don't have the word Name, just leave it blank) if i have to do this for a large data file so i dont have to copy this function for every cell that contains the word Name.

View Replies!   View Related
Count Cells Containing Specific Word
I want code to count cells that contains only specific word like ( do)

View Replies!   View Related
Sort By Single Word In Cells With Many
How can I sort across 2 columns?

As an example.. If I have the word "Text" placed in columns A and B at various rows (mixed with other words)

What sorting order can I use that will sort the entire row, based on both columns A, B. ( would like initiate via vba script)

Begin with this:

A , B

.....,.....
.....,.....
Text,....
.....,.....
.....,.....
.....,Text
.....,Text
Text,.....
.....,.....
.....,.....
.....,Text
.....,.....
Text,....

After sorting order:

Text,.....
.....,Text
.....,Text
Text,.....
.....,Text
Text,....
.....,.....
.....,.....
.....,.....
.....,.....
.....,.....
.....,.....

View Replies!   View Related
Formula To Count Occurences Of A Word In A Sentence Within A Cell
I am after the formula to count the occurrence of, for instance the word 'the' in a sentence/paragraph that is contained in Cell A1. Cell B1 should return the quantity of times the word 'the' has been found in Cell A1.

View Replies!   View Related
Formula To Enable A Letter To Provide A Word As Answer
I have the following letters in cells
D2= A
D3=G
D4 =L

These represent Accomodation,Groceries, and Liquor.
Starting at E12 I want a formula , to drag down the column, which will show either Accomodation, Groceries or Liquor depending on the letter I put in cell D12,

Eventually I will possibly need help to summarise these costs on another sheet with other data such as fuel costs and fuel analysis but this will get me started.

View Replies!   View Related
IF Formula Syntax (cell To Display The Word FAIL)
If the addition of two cells (a1 and b1) do not add up to more than 10 then I would like cell c1 to display the word FAIL

I've tried the following

If (a1 + b1 ) < 11 then c1 = "FAIL"

But it doesn't like it

View Replies!   View Related
Create Hyperlink From 2 Cells, Then Open Word
The purpose of this endeavour is to create a cell that when the user clicks
on it, it will open up a Word file that THAT particular cell represents.

FIRST STEP:
Take the values from Column D (Tract Number), then add a ".doc" extension to
it, then put the UNC filepath (\serverdata
eports) in front of all of that
and put it in Column E. See me example below:

File path plus Tract Number plus Extension

\serverdata
eports 7-5-065-085 .doc

.... to generate something like this:
\serverdata
eports7-5-065-085.doc

SECOND STEP:
Use the value (only when the user clicks on the hyperlink) from the cell in
Column E and start up Word.

View Replies!   View Related
How To Return All Cells (in The Form Of An Array) Containing A Certain Word
I am drawing a total blank here, lets say I have a column of cells:

A1 ("Word")
A2 ("No")
A3 ("No")
A4 ("Word")
A5 ("Word")

(they all have words in them). How can I return all cells (an array?) that contain a specific word?

=COUNTIF(A1:A5,"Word")

returns: 3

That function is almost perfect, except that returns a number - I need this to return an array of all of those cells that contain the word. Because I have another function to run after that takes in cells and THEN counts how many are a certain color (this one already works) - so it obviously cannot take in a number, it needs a list of cells

So it should return:

A1,A4,A5

But I'm not sure in what format

View Replies!   View Related
Find Word In Range Cells Containing Other Strings
I am having a hard time creating a code that would search the exact string in a cell. See sample scenario below.

1. I have a string "hello" to be searched.

2. I will search the whole string in each cell in a previously declared range.

3. If in a cell i found "kdhlsk hellodsiewjls", although "hello" string is there,
the code should still return 0.

4. But if in a cell, i found "kjdsfel ljsdlkue hello luoeww", then the code should return 1.

I have already created a code, but it will always return 1 in step#3.

View Replies!   View Related
Check If Word Is Within Text In Column Cells
I have a worksheet for a mailing list with name, address and other information. I need to search for the text "board" in the name column and if it is found, add a code to a different column.

View Replies!   View Related
Count Of Specific Word In Cells With Many Words
Is there a way of counting how many rows have cells containing a specific word. Some rows will have multiple cells with the word in and some will have none. I only want a count of the number of rows and not the number of times the word appears. How can i acheive this with VBA?

View Replies!   View Related
SUM Cells With Particular Word In Left Cell.
In my column B, I have different words like Balance, Notes, Expense, etc. Is there anyway I can look into column B, and if I get the word Expense, then take the number directly to the right of it (from column C) and SUM it? Not sure if it matters, but let's say I want to put it in cell C2. I figure this will allow me to do this like insert, delete rows in the future, but my equation in cell C2 will still add up what I need.

View Replies!   View Related
Export Cell Contents To Word Fields In A Protected Word Document
Is it possible to export Excel cell contents to Word fields in a protected Word document? For example...

What code would be needed to tell Excel to open up, copy and export the contents of A2 in the active sheet of a workbook to "Field 2" in a Word document named "Report 01" and then put the contents of B2 to "Field 2" etc?

Do both applications have to be opened up at the same time or is Excel able to open up Word on its own? Will the macro be able to....

1. Automatically open up the correct Word document?

2. Look ONLY in a certain folder for the "Report 01" Word document?

or

Bring up a "selection" box that allows you to select the document you wish Excel to export it's data to?

3. Close and save the Word Document without any user intervention?

View Replies!   View Related
Formula The Looks For Name The Gives The Relivent Data As Headed By The Colums Under The Word Average
i need a formula the looks for neame the gives the relivent data as headed by the colums under the wrd average

View Replies!   View Related
Find All Instances Of Word And List Adjacent Cells
I have a sheet where in one column, the word "high" will be repeated. I need to find these multiples and then list down in a seperate column the text relevant to each of those multiples (contained in an adjacent cell). I.e if A1 & A3 hold the text "high", take the corresponding values in b1 & B3 and list them in new column C (C1 & C2 continuing in sequential order). I have searched the forums but i can' t seem to find anything.

View Replies!   View Related
Data Validation :: Cells Should Contain Specific Word On Input
I'm trying to make the included spreadsheet as idiot proof as possible as a lot is a stake. I have most functionality working well; however, not I would like to add a little more data validation that does not exist in the normal cell validation rule set.

I need all the input cells to validate that the cell contains the word "BOX" upon entry, otherwise throw a warning. This is because the data entry will be done by barcode and there are a couple barcode labels on each box. The one I want will include the word "BOX" somewhere in the code sequence.

Since a previous function in the Code locks the cell, they wont be able to fix the error. but it will alert them so they can make sure to scan in the proper barcode and not miss the box. Also, once I get something that works in the code, where would I put it? In the same code box as the previous script, underneath it, or somewhere else? So, bottom line, I need a script that check the cell for Text of "Box" otherwise throw a message box.

View Replies!   View Related
Preserve Cells Word Formatting Concatenating Text
I have a cell with some text in it that has formatting applied (bold in this case). I want to append some text on the end using VBA, but preserve the existing font formatting information. So say I have "one bold one" in my cell, I'm executing the following: ActiveCell.Value = ActiveCell.Value & "two"

But this wipes out the bold information. Is there any way of preserving the bold that's already in the cell. Either perhaps by copying the Characters object and reapplying it, or by some other method?

View Replies!   View Related
Color Row Where Cells In Column Contains Specific Word
I have a spreadsheet with 12 coloums. In the last coloum are the ords "yes", "no", or is empty (null?). I need to find a way to have each row that contains the word "no" or is empty to be highlighted in red font and bold type. When the word "yes" is typed then the font is black and the type in normal. This allows me to see at a glance what orders have not been picked up or have problems.

View Replies!   View Related
Using The IF Function, To Make Entering A Word, Space Then Word Correct
How can I use the IF function, to make entering a word, space then word display correct and incorrect if not.

View Replies!   View Related
Add A Word Or Phrase In Front Of The Data Already In A Column Of Cells
what's the fastest way to add a word or phrase in front of the data already in a column of cells?

Example:

cell contains product description and I need it to show
company name product description

View Replies!   View Related
Find A Certain Word In A String Then Return The Number Associated With That Word
Have problems using find and the Dictionary
What Im trying to do is find a certain word in a string then return the number associated with that word

View Replies!   View Related
Replace Word In MS Word With Varable From MS Excel
I am in the middle of automating a process here at work, the program takes a word, "pencil" for example, from excel. It will then open up a word document with content already in it (premade template). The program will then find all instances of a string, "placepencilhere" for example, and will replace that instance with the string from excel.

Basically I want to be able to take a variable that has a stored string value from excel and use it to replace another variable in a word document.

I tried recording a replace (ctrl+f, replace tab) macro, copying the code, and inserting it into the excel vba code.. but I get a error message. Here is what I have:

---

View Replies!   View Related
If Word Is Contained Within A Cell, Ouput That Word In Another
I have a bunch of cells in column b that have products.

Column B
Dell 24" lcd
vaio sony laptop
8.0 mpxl kodak camera
photoepsonprinter

Basically in Column A I want a formula that'll say.

If the word "dell" is somewhere in cell b1, then put the word "Dell" in cell a1.
If the word "sony" is somewhere in cell b1, then put the word "sony" in cell a1.

And then so on and so forth down through column A. The brand names are potentially endless, so is there a easy way to on like a seperate sheet make a list of brand names and have it pull from there?

Originally I made a if function that said if the brand name was contained in the cell then output the brand name, but that caps at 7 for the amount of brands i can use....

View Replies!   View Related
Formula (search For Colour)
I have a large spreadsheet that is colour coded. E.g. Red means x and Blue means y etc.

Do you know if it is possible to build a formula to count how many red cells there are?


View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved