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:.............
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.
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".
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.
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.
I would love to be able to click on an assigned button in a new sheet, and a pop box appears.
Then I can type in a word, click find, and the macro will go through my whole list of keyword phrases and find all the words STARTING with the word chosen in the pop up box.
Yes, I could just sort from a-z, find the word, then copy and paste etc. But I work with large lists sometimes, and its just so much easier to type the word, click find and the macro returns the list of just the phrases with the starting word I've chosen
As a note. I did put a post up a while ago which a coder called Jindon solved which works perfectly. (A great job by the grand master) This macro finds the words ending with. So, what I would really like is exactly the same macro, but it finds words at the start of a phrases not the end of a phrase. I'll post Jindon's macro now
Sub Find_Words_Ending_In() Dim sTime As Single, res As String, msg As String, x As Range Range("c3:c" & Rows.Count).ClearContents res = InputBox("Enter word to find") If res = "" Then Exit Sub sTime = Timer On Error Resume Next................
I have received a workbook containing lists, unfortunately the names are listed in individual cells - several per cell, separated by commas. I need to highlight wherever a particular name occurs without splitting the data from the cell, ie like Find but within the cell.
I have a spreadsheet with 65000 potential client surnames in column c, with contract details in neighbouring columns. I have also a spreadsheet of 6000 actual client surnames. I would like to search the large spreadsheet to make sure that none of the clients are contained in it, or if some are to highlight them.
Do I use advanced filter or vlookup? I use excel 2000 and due to the size of the first spreadsheet it takes 6 minutes to open/save e.t.c.
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
see attached spreadsheet. In sheet 1 I would like to look up each word in column D, seeing if they are in column B at all. Note if the word "Jill" is in D and "jilly" in a surname in B I would like it to get picked up. I have highlighted manually those that would get picked up. Those that do get picked up I would like to be copied into column C as per sheet 2 (this is what I would like it to end up like). There is a very long winded way of doing this using a find function and 1 column per word but as the actual sheet i'm using has thousands of different words this isn't really viable!
I have two different lists that have some similar text and some irrelevant text. What I need to do is move the matching words from column B to the same row in column a and leave blank the rows that have no similar words. Column A will not move ...
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).
I've been racking my brains trying to find a way of doing this. I have a list (column A in Excel) of over 50,000 organisations and I'd like to know what the most common words used in the names are. Ideally it would great if I could produce a top 10 list of the most common words at the top e.g. Ltd, School or Church with a count in the next column of how times that word it appears
I have a spreadsheet of part #'s, descriptions, manufacturer names, and manufacturer part #'s. (It's a list of the inventory in my warehouse). Each row contains information for just the item in that row. Row 2 references another part in my warehouse, row 3 yet another, and so on.
Many of the parts have more than one potential manufacturer and part #, (meaning that any of those manufacturer's part #'s are basically the same tool; just different brands. At one time we may get a shipment of one, at other times we may get a shipment of another). For example, a screwdriver may be listed like this:
Part # 1234 screwdriver, mfg Snap-On, part # 456, mfg Stanley, part # 789, mfg Mac Tool, part # 439.
Then further down the list, there may be another part listed like this:
Part # 9980 wrench, mfg Stanley, part #741, mfg Snap-On, part # 852, mfg Proto, part # 369.
If you can imagine that data across the cells of a spreadsheet row, notice how the mfg name 'Snap-On' was the first mfg name on the screwdriver, but it was listed as the 2nd mfg name on the wrench.
So, here's my question: I want to be able to group all of the items made by any one manufacturer together in a new list. If all of the manufacturer names were in the same column, I could simply sort the list by that column, but since I've got thousands of rows with the mfg name I'm looking for in different columns on different rows, I thought maybe a macro could search each row for the word I'm looking for, then if found, take the whole row and copy it to a new worksheet. So the end result would be, If I wanted to see all items of which Snap-On is an acceptable supplier, I could get a list of all potential Snap-On items grouped together.
I'm sorry this is so long. I may have over-worded this and it may not be too clear. I could email an example of the spreadsheet if anyone needed more info to figure out what I'm looking for and was willing to take a look at it.
2. Once the entire list is broken down into its many parts, use the pivot table feature of excel to determine how common each of the parts is within the entire data set.
So, my questions are these:
1. Do you believe this is the best way to solve my problem? If not, what would be the preferred method? 2. If this is the best method, what function or script would I use to accomplish the first step of breaking down the lines into their individual parts?
Mike Auto Merged Post Until 24 Hrs Passes;It appears I put too many characters in the title of my post. It should read: Common Words - Decomposing Text Phrases
I have a set of data in once cell and its strung out. I'm trying to pull the numbers that follow certain words from that data. That's easy enough with the find and mid function for the first one. But after that I have a repeating word (see below) and different numbers that follow it. So I want to pull the second number string after that word, the third and so on.
I've attached an excel file that explains this better.
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 am trying to count words in seperate rows,columns in a spreadsheet. I have tried to use the formula that was suggested in one of the formula pages but I did not get the correct answer. I tried to do just like the information said with a1:a5. I did get 5 as an answer but when I deleted one of the words it still said there were 5 words.
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.