Modifying VBA Function To Search Only Complete Words?
Mar 19, 2014
I've been working on a method to search a single column of 1000+ cells containing a paragraph with a 250+ word keyword list (also in a single column). I'm needing only complete words to be searched/ displayed regardless of spacing & punctuation. I've tried multiple methods the past few days and the one that worked (Macro with 4 complex formulas) was unusable when scaled out to all 1,000 rows due to the extremely long calculating time. I don't know VBA yet but was able to modify and create a user defined function that does everything I need except it displays string results that are not whole words (Ex. search for air, word in cell is fair, displays a result for air).
PHP Code:Â
Function RangeSearch2(text As String, wordlist As Range, seperator As String, Optional caseSensitive As Boolean = False)   Â
Dim strMatches As String    Dim res As Variant    Dim arrWords() As String    Dim skip As Boolean    skip = False   Â
On Error Resume Next    Err.Clear    For Each word In wordlist            If caseSensitive = False Then     Â
[Code] ...........
View 4 Replies
ADVERTISEMENT
Jul 7, 2009
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.
View 2 Replies
View Related
Jul 20, 2012
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")
View 3 Replies
View Related
May 27, 2013
I am trying to search words that are in column A (5 letter combinations of sequences) within the text in column B (amino acid sequence).
So I am stumbling upon 2 questions:
- what is the function that would do this search.
- how to acchieve to reverse the text in B1 cell (eg. abcde --> edcba , but with 600 letters),
sample file: test.xlsx
View 4 Replies
View Related
Feb 17, 2010
I want to search say in column A for specific words say “Don” and then write in another column (say column 2) as “one”. Then it should keep searching for specific word until the last cell in that column and keep writing specific word in another column. It should do nothing if that specific word is not found.
I was able to use IF(ISERROR) but there is limitation of max 6 searches. Also i was able to achieve it by so many iteration of IF(ISERROR) and using autofilter.
My goal is to search around 30 to 40 criteria in one shot or by writing macros.
I guess i have presented my problem properly.
View 4 Replies
View Related
Feb 6, 2012
I'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")
View 1 Replies
View Related
Jun 8, 2006
I am looking to search a column of data (text) and have copy relevant cells to another spreadsheet.
Example:
Enter "Cat" in a userform, and the program would copy all of the following rows to a new spreadsheet:
Cat
cat
your cat
the cat
123 cat
cat123
catters
CAT
Think you guys may get the picture...if cat appears in any way in the cell, I would like that cells contents on a new spreadsheet.
View 3 Replies
View Related
May 10, 2009
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 9 Replies
View Related
Jun 9, 2014
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 Related
Nov 7, 2008
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.
View 2 Replies
View Related
Dec 7, 2011
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";
View 2 Replies
View Related
Sep 14, 2009
Using the search macro code below, could someone please help to add in more codes what I'm currently using, and also where to insert it. The Search function works well for what I need and it helps me to locate data. When using the search function somehow it search all sheets within the workbook but I only want it to search an array of sheets when using this macro that is needed to complete the task for what I'm after.
Macro
Public Sub FindText()
'Run from standard module, like: Module1.
Dim ws As Worksheet, Found As Range, rngNm As String
Dim myText As String, FirstAddress As String, thisLoc As String
Dim AddressStr As String, foundNum As Integer
myText = InputBox("Enter the text that you want to search for:", "Start Search!")
If myText = "" Then Exit Sub...................
View 9 Replies
View Related
Sep 9, 2003
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 Related
Aug 21, 2009
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 9 Replies
View Related
Jan 10, 2008
I am using the extract words function outline here [url]
I copied and pasted the function into a new sub and deleted the sub module1 and the end sub to make sure it is it own function I can call. I have called it in another macro I wrote and when I run the macro it bugs out at the first if statement. The code it has a problem with the Get_Word code below. It throws a "Run-time error: '1004': unable to get the Find property of the WorksheetFunction class."...
View 5 Replies
View Related
Jan 18, 2014
I am having trouble with some formulas displaying the results of the logical test as words.. For example.
=IF(E4>F4,100, IF(F4>E4, 200))
That works perfectly but I want "100" to be "Valid" and "200" to be "Invalid"
However...
=IF(E4>F4,Valid, IF(F4>E4, Invalid))
only results in the #NAME? error for me?
View 3 Replies
View Related
Jul 31, 2008
I have an issue with VLOOKUP. By runnig VLOOup we can get the data that there is present in a specified range. But how about to get the data DISPLAYED which is not there in the specified range. I tried combining IF and VLOOKUP functions. I am on a simple project now and I would be happy if I could get the answer immeidately
View 2 Replies
View Related
Dec 29, 2008
I'm trying to write UDF which getting RegEx pattern and a certain cell as arguments and returns only matching string. For examples for string "The quick brown fox jumps over the lazy dog", and RegEx pattern "w{4}" the function will return two words "OVER" and "LAZY". What should I change in my code?
Function GetPattern(myPattern As String, myString As String)
Dim regEx As RegExp
Dim Matches As Object
Set regEx = CreateObject("VBScript.RegExp")
With regEx
.Pattern = myPattern
.IgnoreCase = True
End With
GetPattern = regEx.Replace(myString, "$1")
End Function
View 2 Replies
View Related
Mar 23, 2007
I am trying to parse a formatted text file. I am using the SPLIT function as
arr() = split(cline," ")
where cline is an input line. if the line input(cline) has multiple spaces between two words, how would it split that.
Eg :
abcd defg fefdcs fasdfasdsa
would the output of above be
arr(0) = abcd
arr(1) = defg
arr(2) = fefdcs
arr(3) = fasdfasdsa
View 8 Replies
View Related
Nov 1, 2009
Before I got this Function from your Form to convert digit to words.
Function is as following..right now i m getting as following:-
If i type 123..i m getting one Hundred twenty Three with this script as below.
But i want US Dollar one Hundred Twenty Three..
Can any one add the word "US Dollar" in this script.
Script..
Function English(ByVal N As Currency) As String
Const Thousand = 1000@
Const Million = Thousand * Thousand
Const Billion = Thousand * Million
Const Trillion = Thousand * Billion
View 13 Replies
View Related
May 16, 2014
I have an 'Update' Module that search the value of the Userform1.txt_sc.Value in Column B, but I want to search to 2 criteria, how can i amend the below code to search for 2 criteria ( userform1.txt_sc.value & userform1.txt_linenum.value )
View 2 Replies
View Related
May 17, 2009
I want to have a few words shown based on figures in specific cells. I not quite sure how to explain, but i will try my best:
A1 = 5
A2 = 0
A3 = 7
The function i am using looks something like this:-
=IF(A1<6,"Do I Have Sickness Self Cert?","Do I Have Doctor's Paper?")
However, by using this function, if the cell is empty it still shows the first wording when i need it to show lets say between 1 & 6 shows the first wording and more than 6 shows the second wording! If you know what i mean... I know what i want to do but cant really explain it very well.
Maybe it should look something like this but it does not work the way i am doing it.
=IF(E156>1<6,"Do I Have Sickness Self Cert?","Do I Have Doctor's Paper?")
View 5 Replies
View Related
Jun 4, 2009
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.
View 2 Replies
View Related
Sep 20, 2006
I have a column of words in Column A and I want to replace all the times that these words appear in the rest of the excel sheet with the words in Column B. If someone has already answered a similar problem link me to the thread because I can't find anything.
View 5 Replies
View Related
Jun 3, 2014
I'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.
View 7 Replies
View Related
Jul 16, 2009
I've been using conventional method to do this and it's time consuming. I would like to total up 2 column. A multiply B to be exact. Below are some examples:
Table 1 - Before totaling up:
Quantity
Product
5
2 x Button A White
3
4 x Button B Pink
4
5 x Ribbon A Black
2
3 x Thread A White
6
2 x Cloth A Blue
Table 2 - After totaling up:
Quantity
Product
10
Button A White
12
Button B Pink
20
Ribbon A Black
6
Thread A White
12
Cloth A Blue
I need to have the sum of the "Quantity" multiply "Product". Or in short A x B.
And the end result need to have the number and "x" sign removed while keeping on the the products names. (2 x ) Take note it's "number" space "symbol" space.
View 9 Replies
View Related
Oct 25, 2011
I would like to use the search function in vba to look for a 5 digit number in sheet1(at a specific cell) from a list in sheet2- ie the range. Am I using the range correctly? This code is in a command button in sheet1.
Dim Z As Integer
On Error Resume Next
Z = Application.WorksheetFunction.Search(111, Worksheets("Sheet2").Range("a1:a100").Value, 1)
[Code].....
View 1 Replies
View Related
Dec 4, 2008
I need to create a function that searchs one definitive word or phrase inside of a text in the Excel and creates a vector with the address of the cells that contains it.
Inside of this function, to create the option to change the color of the source or the color of deep, or to erase the indicated cells
Function SearchValue(SearchedValue As String, Interval As Range)
Dim Célula As Range
For Each Célula In Interval
If InStr(1, UCase(Célula.Value), UCase(SearchedValue)) 0 Then
If IsEmpty(SearchValue) Then
SearchValue = Célula.Address
Else
SearchValue= SearchValue & ";" & Célula.Address
End If
End If
Next Célula
End Function
View 9 Replies
View Related
Jul 14, 2006
Can you use the Search function in VB? If so what's the code? Do you have to call it first ( Call Search .....)
View 2 Replies
View Related
Jun 23, 2006
I am trying to find certain words in a column and delete the word and characters following. For example, Say I have a column of info as seen below. This is a test of me. I am just experimenting with this stuff. Deleted (6/15/01) Let me know what you think. I am not sure about it all, but I guess I will figure it out. riviledge1 (01/05/06) Now let's see what happens when I try to test it.
I want to find all the "Priviledge1 (01/05/06)" and replace with nothing. Please note, the date will change with each record, so I need to figure out how to tell Excel to find "Priviledge1", delete it and the date behind it. So I want to delete "Priviledge1" and the next 11 characters including the space.
View 3 Replies
View Related
