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,
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 Replies View RelatedI am trying to code a macro that will search through a selected range of cells for key letters, for instance this cell may contain any combination of B, C, Te, Tc, RH, or LH. I would preferably like to search with capitalization being a factor but it is not a deal breaker. Below is a sample of what i have if the cell has a B, C it works for B but ignores the C i need it t o recognize both.
Code:
If InStr(1, ActiveCell.Text, "B") Then Range("O" + CStr(ActiveCell.Row)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
[Code] ........
I try to search for only one word/phrase in range of cells. I would like formula to return the cell value that contains work/phrase I asked to look for.
For example: A1 contains "Apple", C1:3 contain "Cherry juice", "Apple pie", "Orange bonbon". I want D1 to check which cell from range C1:3 contains word/phrase from A1 and show it to me - "Apple pie".
i can count how many "A" there is in a text put in 1 box and "B" to be put in another box etc.
View 9 Replies View RelatedI have a question with picking out certain word in the column. I am trying to write a formula that would allow me to pick out names that has first 3 alphabetical letters from the columns. The column has a lot of different names, but the one that I would want my automation to pick out is similar to this, C, AR, AA-103, BG-2056, HJE-1100, etc. However, in the same column, I have other words like elevtar, hsvte, lvnrm, etc. that I do not want my formula to pick out.
View 9 Replies View RelatedI 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.
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.
If I had the word OMNICONNECT in cell A1, is it possible to put a formula in B1 that would return OMNIC ?
View 7 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'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'm looking for a macro to remove all words (in a single word per cell format) in a range (approx 100 columns & 7000 rows), except for a list of 100 words.
I'd prefer to email the file if that's okay.
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.
Junior Fit Softstyle T-Shirt
Antique Cherry Red
Junior Fit Softstyle T-Shirt
Antique Cherry Red
Softstyle T-Shirt
Antique Heliconia
Softstyle T-Shirt
Antique Heliconia
Softstyle T-Shirt
Antique Heliconia
This is column a and b. Looking at b I am looking for a formula that will pull the first capital letter out of each word like ACR to create color codes.
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 have tried Sumproduct and Countif and can not get a proper count of rows that contain specific words in multiple columns. I have attached an example spreadsheet where I am looking for a result of 7 rows that contain 1 to 4 specific words within phrases. The example looks for the words: "virus", "spyware", "malware" & "adaware", and ignores any other words like "aware" or "ware".
View 7 Replies View RelatedI have a list of global e mail addresses which are listed as
Joe Lewis/NL/BNRO/NL
Rocky Balboa/BR/BNRO/NL
What i need to do is to highlight any name that has the letters BR,UY,or PY after the first back slash, so in this instance Rocky Balboa needs to be highlighted
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 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
I 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
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 3 Replies View Relatedis possible to have a drop down list where you can type in the first few letters of a word and have the drop down list go to the the words that begin with those first few letters instead of having to scroll down the entire list to find the word / item you are looking for.
I ask since the drop down list may end up having up to 1000 words in it.
i have wordlists which i would like to enhance through texts. For that purpose i need a macro which reads a text and splitts it into words and put the words into three columns. Lets assume the column where the words are supposed to be put is columns A,C and E. The column A should contain single words containing no spaces from Text, column C should contain compound words which have one space between them and in column E those compounds which have two spaces. Now i will put my existing wordlist in column G before i start splitting texts. The macro should put only those words from text into column A,C and E which dont already exist in columns A,B,C,D,E,F and G. In Column B i will place the corrected or checked words from column A. In column D i will type the corrections and checked versions of those compunds existing in column C. And the column F will contain the compound words which are corrected and checked from Column E.
Column A--> single words splitted from text through macro (containing no spaces)
Column B--> corrected and checked versions of words from column A through user
Column C--> compound words containing one space splitted from text through macro (for example:"etwas möchten" or "do something")
Column D-->corrected version of C through user
Column E-->compound words containing two space splitted from text through macro (for example:"sich uerberzeugen lassen" or "down in dumps")
Column F-->corrected E
Column G--> existing dictionary wordlist through user
Signes like ".", ":",";", "/" or quotation signe itself should be seen as seperator. And the words or compounds splitted from the text should not contain these signs.
The text will be put in another sheet in the same excel file. Lets call that sheet "text". The macro should search for words through whole sheet ignoring empty cells or columns.
None of the columns should be filtered or ordered. The columns shound not be deleted as well. That way the user wont have to correct the same word each time cus the corrected as well as the original words will be there.
A thread with similar purpose was made sent by AdamDay as well, where rylo has posted a good solution already.
how to accomplish more work quickly is proving difficult. I am making a word cloud of survey responses for my boss and he doesn't like to see similar words in the cloud (like Ease and Easy or even Fast and Quick). I have several hundred of these responses that are open ended answers that say one or many of these terms. Any way I can separate all the words and have them in one neat column? If I can get to that point, replacing those words will be simple enough.
View 2 Replies View RelatedI would like to search for a certain pattern that appears in a cell. For example, I have certain cells that begin with a 7 digit project code (Ex: "1234567 - Project Red"). I was planning to use an IF inside a FOR loop and then do an action when I find the cell with the pattern. I thought I remember it being something like Format(########*) so that I have the wildcard on the end since there is more than just the numbers. how to make other patterns work such as Number-Letter-Number or Capital-Lowercase-Capital
View 3 Replies View RelatedLooking 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 need a functionality in Excel, but I can't get it done. I want to search multiple titles on multiple words.
1. I have a list with titles (tab A)
2. I have a list with words (tab B)
Now I want to scan all titles on words that appear on tab B and return the value of the word.
Example:
Title (tab A - column A)
Return (tab A - column B)
Words (tab B)
BrandX Shampoo Big
Shampoo
Eau de Toilette
BrandX Nailcutter
Nailcutter
Nailcutter
BrandX Eau de Toilette
Eau de Toilette
Shampoo
So for row 1 the word "shampoo" is reconised in the title and returns this value. Same for the other rows. I need the functionality like the formula below, but then with more words (from a list). I don't want to work with "OR" because the list with word is about 200+ and will be more in future.
=IF(ISERROR(SEARCH("shampoo",A1)),"x","shampoo")