So I have a cell with multiple words in A1. I want to check for the word "red" in A1 and if red exists return a value of "red" in B2, IF "red" is not found then search for "blue", if "blue" exists return a value of "blue" in B2, if "blue" is not found is not found then search for "green", if "green" exists then return "green" in B2, if none exist then return value of "none"
View 14 RepliesI'm trying to find if the description in Column L has any of the following words: (irrigation, sprinkler, water). The cell does not need to have all of the words, but just 1 to make the IF formula return a yes. view the example below and let me know where I am making the mistake. I don't want the case to be sensitive.
This is what I have so far, but it is only finding the first word.
This example returns nothing:
Text in Column L
Main irrigation line broken Need to repair
Formula in Column S
=IF(ISNUMBER(SEARCH({"sprinkler","irrigation","water"},L203)),"Yes","No")
This example returns Yes:
Text in Column L
Irrigation broken sprinklers and broken lateral li
Formula in Column S
=IF(ISNUMBER(SEARCH({"sprinkler","irrigation","water"},L210)),"Yes","No")
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,
I am using excel 2007. I have a worksheet with a list of words I wish to step through this list replacing the selected words in another workbook with nothing (ie deleting them).
I have the following code
Code:
Sub replacewords()
Dim MyWord As String
Dim wbLibrary As Workbook
Dim wbWorking As Workbook
Dim myExcelColumns As Integer
Dim myExcelRows As Integer
Dim MyRow As Integer
Dim MyRange As Range
[code].....
It does not seem to evaluate "myRange";
On sheet 2 in cell C5 I need it to find it from sheet 1 referring it from the word "Drinks 1" thats in Q1 and so on along the row
View 9 Replies View RelatedI have a column of address data that looks like
1924 ogden place blvd west unrulu
982 n aoto st apt #1234 easrlr
...
and I'd like to use a couple of formulas in the two columns to the right of this data
1st column:a formula to obtain the number of words in a cell
2nd column:a formula to extract the last n words from a cell (ex. the last 2 words)
so that the columns would have as their results
6 west unrulu
7 #1234 easrlr
Looking to find 1 of 2 words in a cell in column B and return the word found in the same row in column E. This seemed easy but I am not having any luck.
the cells in column B have several words in them but I am looking for 2 specific words "PLAT" and "ORIG". If the word is not in the cell, it should show a blank cell in column E in the same row, otherwise one of the 2 words should be in that row in column E. A VBA loop would be ideal but a formula that can do it might work as well.
I Want to fetch a data as below.
ITEM RESULT
M&C_ES_F_55+_Phase1 <55>
M&C_BR_F_18-34_Phase1 <18-34>
M&C_ES_F_35-54_Phase1 <35-54>
Likewise there are many line items, wherein i want the Age should be searched in ITEM column and should be displayed in the RESULT column.
* I don't want to Use text to column method, is it possible to use IF condition along with Search formula.
My current formula is this: =SUMPRODUCT((Str311A=1)*(Str311C=15)*(Str311G<>" DM "),Str311L). All the items starting with Str311 are formula's associate with a name. What I'm trying to do is add one more factor into the formula. I need it to look in column I for any cell that contains the words L/S. I tried modifying the formula to this but it does not work:
SUMPRODUCT((Str311A=1)*(Str311C=15)*(Str311I="*L/S*")*(Str311G<>" DM "),Str311L)
The formula contained inside the names are:
Str311=OFFSET(Outlet!$A$1,MATCH("STORE # 163311",Outlet!$A:$A,0)-1,0,MATCH("TOTAL FOR STORE : 163311 SAN MARCOS",Outlet!$A:$A,0)-MATCH("STORE # 163311",Outlet!$A:$A,0)+1,1)
Str311A=OFFSET(Str311,0,0)
Str311C=OFFSET(Str311,0,2)
Str311I=OFFSET(Str311,0,8)
Str311G=OFFSET(Str311,0,6)
What I need is a way to modify my original formula to now also only match cells that contain the phrase L/S. The phrase can be located at the beggining, middle, or end of the cell characters.
I have hundreds of records of customer info and I am trying to categorize the job titles so that I can conduct some analysis with a pivot table. My problem is that there is about 20 different ways and variations of "manager" so Instead of autofiltering for "mrg." "Manager" "marketing manager" ect. and then replacing the text manually and using the enter and fill process (to create some standardization to compare "manager" to "director" to "c-level" to "consultant", etc.) and repeating this process over and over again...
I would like to create a VBA that would search the column "job title" for multiple text strings at once and if the text string was true in the cell then the cell would be replaced by a new text string "Manager". I was thinking a series of if functions within one vba but I am not sure if this is possible.
Search Column A for a name (using an array if possible). If it finds this name, add a string of text to the AA cell on the same row that it found the name on.
For example,
If in A1 it finds the specified name. It then adds a string of text to AA1.
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 6 Replies View RelatedI have a small problem which i cannot seem to figure out. I want to create a code to search for a word in a certain column ie. "L". The row of the columns change until they hit the end of the list. What I want this macro to do is find the word and then if the word matches to leave the cell alone otherwise I want it to hide the cell. Basically something like this:
If Cells(3,12) = "JF" then do nothing else hide cell.
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]
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.
I've got a long list of company names, i'm trying to create a formula to see if they match but the problem is the names won't match exactly (e.g it would be Joe Bloggs Limited & Joe blogg's ltd). I've thought about it and think the best way to see if they match would be to take the first word from the first cell (in this case Joe) and then search for it in the next cell and return TRUE if it exists anywhere in the cell and FALSE if not.
View 3 Replies View RelatedI am trying to automate the below process:
1. I have a excel file with Japanese words in column A and their English equivalents in column B.
2. I am trying to create a Macro and assign it to a button. On pressing the button Macro should be able to:
3. open a form where I can enter location of a word file.
4. Macro should open the word file specified in (3).
5. Macro will pick up first Japanese word from excel file (Sheet 1 - A1)
6. Search for that word in Word file
7. Replace the Japanese words in Word file with their English equivalent from excel (B1)
8. Then it should search for second word (A2) and replace with its equivalent (B2) and so on till it reaches last filled cell in excel file column A.
After a lot of search I could find a code from net (Below), made a few changes, but it is not working.
========================================================
Private Sub OK_Click()
' Requires a reference to Microsoft Word xx.x Object Library
Dim sFile As String
[Code]....
I want to be able to lookup if anywhere in a cell contains a word from a list of words, and then provides an output.
Column G:
VAT payment
HMRC payment
Pay VAT
I have a table on the side that shows:
Column Y Column Z
VATHMRC
HMRC HMRC
ie. If anything in column G matches one of the words in Column Y, then output the Column Z. I have use a Vlookup that works for the first two, as VAT is the first thing, but dont know how to make it work if the key word is in the middle of the cell.
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 9 Replies View RelatedI would Need a macro which would Search a Keyword in the excel sheet and copies and pates the data in Cell "A2". for Example "Market" and then after the search it copies 12 rows upwards and 10 columns from the Cell that the word "Market" is placed. And then it copies 12 rows downwards and 10 columns from the Cell that the word "Market" is placed.
I have tried recording the same but it does not work if the Word "Market" is placed in different cell value.
Working with external documents such as MS word is not my specialty and I have a feeling that what I need has been done before.
1) Takes a word file (open)
2) Split all words and put these in an array
I think a space character can be used as a delimiter, but all characters like ;,:" etc need to be trimmed so only "real words" remain.
I have two worksheets. The first contains a list of software (some having duplicate listings) in column A and a list of comments in column B. None of the software titles that have duplicates will have comments.
The second worksheet contains a condensed list of software (the previous list without the duplicates) and a column for the comments.
I need to have a formula that populates the second worksheet comments cells with the corresponding comments from worksheet one only if:
(1) The software title in worksheet two matches the software title in worksheet one
(2) The comment cell in worksheet one contains a comment (or text)
I have had only partial success; my obstacle being the duplicate software titles in worksheet one.
I have two lists, one has 250 items, the other 4500, both contain a String
I need to compare any word within a cell in the short list, against any word within a cell in the long list.
The returned value needs to display both the short list item, as well as the long list item for manual comparison by an analyst.
I am in need of a forumula. I am trying to extract one word before a keyword, one word after a keyword, 2 words before a keyword, and 2 words after a keyword.
For example:
The keyword is blue:
birds are blue today - 1 word before=are; 1 word after=today; 2 words before=birds are; 2 words after=today
blue is my favorite color - 1 word before=(null); 1 word after=is; 2 words before=(null); 2 words after=is my
see blue colors in the sky - 1 word before=see; 1 word after=colors; 2 words before=see; 2 words after=colors in
Let's say i do have words : cow, dog, giraffe, elephant, parrot.
The rest will try to explain in the excel file.
I am very comfortable with SUMIF, but need to search a range any any cell containing words or phrase "20%" then need to sum those corresponding cells only.
I.e., something like: SUMIF(A1:A10,..."20%",C1:C10) .... only sum all C cells where an A cell contains any combination of words with "20%" in them.
What formula should I use to search a certain text that might be on the beginning, middle or end of a cell to use it as a condition for an IF formula to return a related description. In the example below the text would be "deposit".
View 2 Replies View RelatedI have a list of words some Title cased, and some camelCased. Each cell contains one word. I need to write a macro which if encounters a cell with camelcased word like "ExcelMacros" or "excelMacros" would add new row and add "excel" and "macro" to their own cells.
Is looping each cell for the character code the way to solve this problem or is there a better way?
I have pulled a task of Content Managent. we are going to look at a bunch of files for a key list of words. Is there a way look at the text in a file and find a word within it?
Reporting the link to the doc.. display ... will all come later.
I know it will be slow and may run for a day or two but I would run it over the week end.
I have a sheet called "Words Ending In"
In ColA From A 3 downwards I have a large list of keyword phrases.
I am looking for a macro where I click on a assigned macro button and a pop up box appears saying
" Find words Ending With"
In the space provided I insert a word.
So, for example I insert the word "rent".
The macro then looks at all the data in ColA (From A 3 downwards) and searches for all keyword phrases ending with the word "rent".
If it could return All Phrases in ColC (From C3 downwards).
If it can't find any, then if a pop up box can appear saying something like "No Data Found"
That's it really.
If it could have a pop up box at the end saying
"Total Phrases Scanned: 1234"
" Total Phrases Found Ending With "In"
"Time Elapsed (sec.): 12.12353