Find First Two Words In A String And Compare To Another With VBA?
Mar 28, 2014
What I am trying to do isn't very complicated. I started on some code, but it's just not good enough to do all of what needs to happen.
A
B
C
1
Cupcake Monster
Aisle 5
Shelf H
2
The Cupcake Ghoul
Aisle 2
Shelf P
3
Fred Baxter's Diary
Aisle 1
Shelf X
4
Angry Cupcake Beast
Aisle 3
Shelf A
5
Everyone Loves Cake
Aisle 4
Shelf R
So I have a list of titles that I want to search for a particular string. The title in this list is the "proper" title,whereas the rest of my workbook typically uses a shortened version of the title. Sometimes the two will match 100%, but usually, they will be close.
If the string in the table above has more than 2 words, I want to use the first 2 words to check for a match. If the string has 2 words (will never have fewer), I want to match the first word. I want to do this IF there is no 100% match (if possible or reasonable). I will settle for just matching 1 or 2 words. I want to copy the values to another sheet and then delete the row that I originally got the data from.
What I want to do is search the list for "Angry Cupcake" and return the Aisle and Shelf location of Angry Cupcake Beast. There will never be any other "Angry Cupcake", so I am not worried about mismatches there.
Code:
Dim SearchRow As Integer
Dim StoryTitle As String[code]....
how to do and can get to work without issues is an exact match. This still leaves out all of the shortened titles though.
View 3 Replies
ADVERTISEMENT
Jan 31, 2008
I need to compare a list of keywords (stored in a string variable) with the content of a cell in order to see if any of the keywords do appear in the cell. I have to work with many such lists which can grow over time. What I'm doing is using InStr(cell with content, keyword) for each keyword. My goal is to have a macro which can automatically create the long formula from any list. My actual problem is that the formula I've build and stored acts more like a string. I'd like it to say either true or false. Anyway, here's a sample of what I came up with so far.
Sub build_formula()
Dim i As Integer, j As Integer, m(20) As Integer, n As Integer, z As Integer
Dim List As String, f(2) As String, s(20) As String, sp As String, Source As String
Dim Formula As Variant
List = "black, blue, green, red, yellow, white, z"
Source = Cells(3, 1).Value
z = InStr(1, List, "z")
i = 1
n = 1
Do
i = InStr(i + 1, List, ",")
m(n) = i ..........................
View 5 Replies
View Related
May 28, 2014
How can i find the first 15 letters or numbers in a text string "material change:"
or find ":" then display everything to the left of that symbol ?
assume formula in a1 and test string in b1.
I wish there was a class you could take on this stuff.
View 9 Replies
View Related
Feb 26, 2014
I have uploaded my spreadsheet.
I have a column of text strings on Sheet1, Column A, which I need to check for the presence of keywords listed on Sheet2, Column A
So if any word from the keyword list on Sheet2, Column A is found in, say, cell A2 of Sheet1, the cell to its right (B1) should have a formula to display the count of keywords found in A2. I also would like to see each keyword identified either through a highlight or a list. I need the formula to NOT be case sensitive and the match does not have to be for whole words).
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
Aug 23, 2007
I have a problems here. The problems is attached in the file. I wanna extract alpha/char from a string. Example: I wanna extract the words "(M)" with the bracket from the string "Toothbrush (M)" in column A. After extracting the (M) out, I wanna do a validation to compare the (M) in column B with another data in column C, if the (M) is same as the data called "Medium" in column c, the validation will return "Match" in the column d!
View 9 Replies
View Related
Apr 19, 2010
I have a column with 10,000+ lines, and lots of them are duplicated.
I have a formula that tells me how many times they are duplicated, but it only looks for 100% matching cells.
Now, lots of cells are partially duplicated, for example:
A1 - Team Abcde
A2 - Team Abcde Fghi
B1 - Team Abc
I would like to have a formula giving me a percentage of how two cells are similar to each others by checking the words. In this case, B1 is 50% similar to A1 because only the word 'Team' is duplicated. B1 is only 33,34% similar to A2.
View 14 Replies
View Related
Aug 25, 2013
I have an issue with case sensitive while creating forms in Excel VBA. When i compared the text "EXCEL" and "excel" it showing as both the words are not equal/same.
Is there any code to compare the words/inputs without case sensitive.
View 2 Replies
View Related
Feb 15, 2008
I'm attempting to extract the Street Names from an Address.
For Example, if given "123 Very Big Street" I'd want to extract "Very Big".
If given, "123 Very Big" I'd want the same answer.
I've written code to do this, with a simple IF statement to identify mathcing last words like "Street" and "Drive".
How can I efficiently do this with a large list of last words? I'm drawing a blank on how to query against an array that would contain all these values.
View 12 Replies
View Related
May 10, 2014
Take a look at the attached workbook.
Tab 'Internal' is an example of our internal customers and how much they spent with us.
Tab 'External' is a list of companies in our area that we could do business with, it came from a marketing firm. Columns B:J contain my formulas. B is my lookup.
I need to match the two sheets together and pull the dollars spent from Internal to External. The problem is that the marketing firm did not use the same naming conventions that are in our systems. This means a simple Vlookup will not work.
Here is what I did so far: used VBA and formulas to break apart the company names in External, then using a modified Vlookup to match the words to the names in Internal. My accuracy rate is less than 1%.
View 1 Replies
View Related
Feb 22, 2013
I have a conditional formula which populates a string depending on the value selected by user.
E.G of formula is:- =IF($B$1=0,"CONTACT PERSON - OPTIONAL",IF($B$1=5,"CONTACT PERSON - MANDATORY",""))
Where the result is "CONTACT PERSON - MANDATORY" then I want the word MANDATORY (not the whole string) to be displayed in RED.
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
Oct 10, 2013
I have a sheet of about 10k rows that I need to switch some things around on. The string is below:
Acura|TL|1995^^Acura|TL|1996^^Acura|TL|1997^^Acura|TL|1998^^Acura|TL|1999
It needs to say:
1995|Acura|TL^^1996|Acura|TL^^1997|Acura|TL^^1998|Acura|TL^^1999|Acura|TL
How to fix this with a macro or formula. The Make Model and Year on each string will be different. Basically I need the format to be Make|Model|Year.
View 9 Replies
View Related
Oct 13, 2013
Let's say i do have words : cow, dog, giraffe, elephant, parrot.
The rest will try to explain in the excel file.
View 9 Replies
View Related
Apr 30, 2014
I can not get this to work:
Code:
Sub SplitApart()
Dim data As String
data = Sheets(1).Cells(20, 1).Text
For Each EachSplit in Split(data)
n = n + 1
Sheets(1).Cells(20, n + 1) = EachSplit
Next
End Sub
Error code when Debugging on "Split":
"Wrong Number of arguments or invalid property assignment"
View 4 Replies
View Related
Mar 8, 2007
How can I count the number of gaps (spaces) of a string? (Adding one we have number of words!). I need to save the position of each gap (space) on a array. How can I do it? With InStr()? The variable (string) that i'm working is GlbTargetRange.Value
View 4 Replies
View Related
Jun 23, 2014
I need to extract all instances of words that have format xnnnnnn, where x is an alpha character (letter of alphabet, to be precise) and nnnnnn are numbers. The words could something like u435586. The problem is I do not know how many instances of these words are in the string. The entire string is contained in a cell. A sample string could be something like:
SMNTv922970;#1283;#SMNT 433925;#1284;#SQRS 003417;#1285;#SQRSp047683;#1286;#SMNT 6132451;#1287;#SQRSw3145627;#1288
and the end result should be
v922970 t433925 t003417 p047683 t6132451 w3145627
The words are preceded by the character "" which might facilitate the search.
View 2 Replies
View Related
Jul 14, 2014
This code is not accepting the values which is starting from strings like this
for example
bearing 15/16 IN LG, 1-1/16 IN OD, 11/32 IN THK,21/128 IN ID
19/128 IN LG, 2-3/64 IN OD, 1/2 IN THK, 5/64 IN ID
steel 15/16 IN LG, 1-1/16 IN OD, 11/32 IN THK,21/128 IN ID
19/128 IN LG, 2-3/64 IN OD, 1/2 IN THK, 5/64 IN ID
spares 15/16 IN LG, 1-1/16 IN OD, 11/32 IN THK,21/128 IN ID
[Code] .....
View 1 Replies
View Related
Jun 18, 2008
I have a text string with 3 data to be extracted and then split throu range, but only specific parts is needed.
string is like that:
Name Surname - Number : ################ - Exp : YYMM - Centre etc etc....
Lets say its allways happens in cell A3, for example:
Adrian Kukuwas - Number : 1235467890123456 - Exp: 1009 - Centre... I would like a macro:
*that runs only if string starts with a letter, then if so
*takes Adrian Kukuwas to cell D3
*takes 1009 (the exp) to B3 and makes it look like 09 10 (or 09_10)
*in A3 leaving only the number 1235467890123456 text formated (sometimes the copy source formats the cell for bad)
View 9 Replies
View Related
Dec 5, 2013
I have two lists, one that is 99,000 lines and the other is 150. I am trying to find out where the word(s) in the list of 150 is present in the list 99,000.
1st list of 99,000 is in Sheet1 A1-A99000 and the second list is in Sheet2 B1-B150.
The caveat is that in column A there is additional words in that cell so you can not do a simple vlookup, because there may or may not be an exact match.
I have been stuck for hours and the best i can get is to use the match function but it is not working because it is not exact. Last thing i want to do is use the CTRL F key to lookup all the words.
View 2 Replies
View Related
Oct 9, 2008
I want to make a lookup on a string of words from one of my worksheets and I also whant to make a hyper link so that when I click on the lookup command I should pop to the other worksheet were my database is.
View 2 Replies
View Related
Oct 22, 2012
black.blue.red.yellow.green.white
I'd like to extract just "blue.red.yellow.green" to the following spreadsheet column, keeping the intermediate dots, but getting rid of the first/last words and their succeeding or preceding dots.
I've tried using RIGHT, LEFT, MID formulas unsuccessfully
View 3 Replies
View Related
Nov 1, 2012
I have extracted a string from my address database which goes like name,address1,address2,city,postcode,country
I need to display in Column B as:
name
address1
address2
city
postcode
country
how to do this using VBA.
View 2 Replies
View Related
Aug 4, 2014
I want to compare a string in a different workbook. and find the relevant cell value of that.
e.x. in one worksheet the value displayed like "DEBIT/ATM CARD TRANSACTION FEE" But in another worksheet the value displayed as "018 - Debit/Atm Card Transaction Fee".
Now I want to compare this string and the value displayed in the column E for this row of workbook 2 should be displayed in workbook 1 in the column D.
View 6 Replies
View Related
Jan 24, 2014
I am working on a project where I need to remove records based on certain criteria.
The key pieces of information here are the Company ID, the Contact ID, and the Product Codes. Each Company ID can have multiple Contact IDs, and each Contact ID and have multiple Product Codes. (To be more clear, imagine one company with a Tax department and an Audit department - with each department having a separate contact person. And, each contact person purchasing multiple products.)
I have already summarized the data by identifying the unique Contact IDs and combining the corresponding Product Codes into a string.
Now, I need to determine if any Product Code within the summarized string matches a Product Code in a separate list (a Range of cells). If so, each instance of the Company ID needs to be moved into a different tab. Also, to understand the magnitude of my records, I am working with 25,000+ unique records in the first tab, 8,000+ summarized records in the second tab, and 50+ product codes in the separate range in the third tab.
My first thought was to use a For loop to determine if any cell in the Range is found in each string and, if so, add the related Company ID to a dictionary object. Then, run another For loop against the Company IDs in the summarized tab (8,000+ records) to determine if the ID exists in the dictionary and, if so, moving the row to another tab.
My second thought was using the original data (unique records) - I would load all the Product Codes in the separate Range into one dictionary and run that dictionary against the individual Product Codes in the unique records tab (25,000+ records). If the Product Code in the unique records is found in the dictionary, I would add the related Company ID to another dictionary. Then, I would run a For loop comparing the second dictionary to the Contacts IDs in the summarized tab and move the ones that exist.
In summary, the two ways I have come up with to accomplish what I need includes:
1) a For loop, a dictionary, and another For loop
2) two dictionaries and a For loop
View 6 Replies
View Related
Mar 27, 2014
I am trying to to work out how to compare two string variables that I can see with my eyes are clearly a match but getting the right syntax to convince VBA they are the same is my issue.The first example is:
Direct Credit 158824 BRANDON DONNA C/ Lak30
the second example is:
Direct Credit 158824 BRANDON DONNA C/ 30 Lake Vue Parade
but that still would not get a match that I can find code for.
i am chasing the "holy grail" in VBA and I will leave the records that do not exactly match to manual processing although i hate to give up that easily
View 2 Replies
View Related
Apr 18, 2013
Some data
In Cell A1 This is a bag
In Cell B1 This is a bag with books
now in Cell C1 i need the mismatch words With books
C1 should show with books
I am now manually finding the words of cell A1 in Cell B1 and then writing the mismatched words C1 .
View 3 Replies
View Related
Jun 17, 2009
I am using the code below to search an Excel worksheet for a pre-determined word and then delete the row the word is found in. It works fine, but I what I really need it to do is search for several pre-determined words and delete the rows.
View 6 Replies
View Related
Dec 8, 2006
I need a way to do the following in Excel:
column A has text
column B needs a Formula that returns specific value(s)
ie. want to find any of 17 specific strings within a row in column A and return that (those) string value(s) in column B
for example row1 column A contains the following:
Troubleshooter Notes: Sound KVSND Tree was not used as doc exists that covered the specific issue
The formula in column B needs to be able to find any of the following values (note: these values could be placed in a lookup table):
"KVBAT" "KVBLTH" "KVDNW" "KVDOC" "KVDWB" "KVFLSH" "KVKBD" "KVLAN" "KVODD" "KVOTH" "KVPFS" "KVRCVY" "KVSB2" "KVSFT" "KVSND" "KVVID" "KVWFI"
and place the found value(s) in row 1 column B
in the above example column B would return the value:.............
View 3 Replies
View Related
May 28, 2009
I was trying to compare string from excel spread sheet to a value I am not getting any output. It is not working
View 4 Replies
View Related
