VBA - Search For Multiple Text Strings In Data Field And If Matched Return One Word
May 7, 2014
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.
View 8 Replies
ADVERTISEMENT
Mar 11, 2014
I have a spreadsheet with 1,000 rows of data.
Each Cell in Column A has a different long text string.
I need to see which (if any) of 10 specific small text strings exist within each long text string. Depending on which small text string is found I want to return a 3 digit code. If no small text string is found I want to return "Not Found"
E.g.:
- Cell A2 contains "randomtext,randomtext,APPLE,randomntext"
- I want to see if Cell A2 contains any of the words APPLE, ORANGE, CARROT.
- I want to return "APP", "ORG", "CAR" or "Not Found"
Q: What is the most elegant way to accomplish this within a single formula that I could paste into each cell in Column B?
View 2 Replies
View Related
Feb 4, 2012
I want to search all the terms Column B within all the text phrases in Column A. I am ok if the words do not match the case sensitivity as I can address that myself.
If the exact phrase match is found, return the value True or False.
Column A
A1:Dog likes to run
A2: Cat Red-tummy-01
[Code]....
View 9 Replies
View Related
May 23, 2013
I have a spreadsheet that has relatively clean data, but at the end of every row is a long notes fields (often filled with several paragraphs of text). I'm trying to search inside each one of those notes to see if it contains certain boilerplate language / legalese / key words.
If I was just looking for one word, it would be easy -- I would write =IFERROR(IF(SEARCH("keyword",E2)>0,"Yes",""),""). That way, if the keyword is present, it returns "Yes", and if it's not present, it returns a blank.
What I want to do, though, if look for a long list of keywords simultaneously, and if ANY of those keywords are present, have it return a "Yes".
So I could do something like =IFERROR(IF(or(SEARCH("keyword1",E2)>0,SEARCH("keyword2",E2)>0,SEARCH("keyword3",E2)>0,SEARCH("keyword4",E2)>0)),"Yes",""),""), but that seems horribly inefficient. Especially since my list of keywords is likely to change over time.
So what I want it to do is search each cell by simultaneous reference to an ever-changing table of keywords (call it [KeywordTable]). And I can't figure out how to do that. The search function is resisting all of my efforts to put multiple search values / a range of words inside of it.
To reiterate: the goal is to look at one cell filled with text, ask "does the text in that cell contain any of the keywords contained in [KeywordTable]", and if the answer is "Yes" return yes, and if the answer is no return no (or blanks).
View 7 Replies
View Related
Mar 22, 2014
How many text strings in a reference column appear in paragraphs of text listed in another column within excel.
So column A contains text strings such as:
{{Advisor_Signature__c}}
{{Advisors_Initials__c}}
{{Advisors_Job_Title__c}}
{{Age__c}}
And I need to see whether any of these appear in cells in a reference column G. If they do, I would like to return 'Used' into column B.
An example of the type of text in each cell in column G is:
"If you have any questions regarding your offer, please contact me. For any questions regarding your benefits, payroll or company policies and programs, please contact HR. Sincerely, {{Advisor_Signature__c}} {{Advisors_Job_Title__c}}"
I don't seem to able to search for a text string across multiple reference cells.
View 3 Replies
View Related
Mar 7, 2014
I need a formula or array to perform the following in Excel 2010:
I have 2 worksheets "Master Report" and "Free text comments". The master report will be shaed with individuals but the Free Text comments worksheet will not.
sample feedback report.xlsx
In cells D2 downwards on the "Master Report" I want to show the individual feedback comments from the "Free Text comments" worksheet that have been made by other people for the individual named in cell A2 on the "Master Report" worksheet. So on James' master report it would show the 8 individual comments listed for him on the Free text comments" worksheet.
I have searched the internet and tried various combos of lookups, IFs, index and match but have not come across the right formula yet.
View 1 Replies
View Related
May 20, 2009
Im needing to search through the attached document which is truck logs at a mine and use vba to search through the list for each of the different shovels eg SHVL1, SHVL2 & SHVL3, and when the code finds that string, to display the tonnage 2 colums back from it. Im about half way there a i think but am having trouble with strings, if it was numbers i could do it no problem
View 11 Replies
View Related
May 7, 2013
I have a workbook containing multiple sheets for documents issued to manufacturers. I have a summary sheet which contains all of the documents and I would like to be able to look up a document number against all the sheets, and return the name (or names if multiple finds) of the sheets containing this document.
I have got close to solving this by finding another post[URL] .... but I believe this doesn't work because it searches on values rather than matching text.
I have attached a simplified example file with the sheets to search being Fab 1, Fab 2 and Fab 3, and the formula need to go into column B of the Main Sheet.
Example for Ozgrid Forum.xlsm
View 6 Replies
View Related
Apr 8, 2013
Here is an example of the data I get each day Letter order granting Sabine Pass Liquefaction, LLC's et al 4/16/12 request to add an alternate water source etc under CP11-72.Letter order granting Cameron LNG, LLC?s 4/5/13 filing of a request to introduce natural gas or process fluids into the BOG Liquefaction Project under CP12-15.Letter order accepting NorthWestern Corporation's 8/7/12 submittal of revisions to its transmission planning process to comply with the Commission's June 8, 2012 Order under ER11-2932.Letter order approving Public Service Company of New Mexico's 12/7/12 filing of a joint Offer of Settlement with Navopache Electric Cooperative, Inc under ER11-4534 et al. How can I set up my spreadsheet and what formulas can I use to search and return a value for each text string based on the attached table (column B)?
E3a7qta.png
View 1 Replies
View Related
Dec 13, 2007
Need to do (preferrably as a UDF so I can call it many times and build the macro I really need) the following string testing macro. For a given column (B) - look to the field immediately to the left for a text string. This string will be searched for a list of other strings contained in Column D ( cells D1:D5). We want a case insenstive SEARCH (or find I suppose in macro terms) to return a true if the strings are found or false if it is not. Example test string in cell B1 would be:
I am a string of test data to check.
Terms in D1:D5 might be:
dog
cat
bird
data
fowl
Cell B1 should return a TRUE as a match.
View 2 Replies
View Related
Sep 16, 2013
I have a single column with multiple rows of data that contains multiple sports words (e.g.: football, basketball, track, hockey, swimming, skeet), and I want to find all cells that contain football, basketball and track. I also want to ignore case.
In this example only 3 rows would be found (3, 4, 8).
Sports Terms
------------
1. football, swimming, skeet
2. football, basketball, skeet
3. football, basketball, track, skeet
4. football, basketball, track, swimming, skeet
5. basketball, track, hockey, swimming
6. football, hockey, swimming, skeet
7. hockey, swimming, skeet
8. football, basketball, track, swimming
9. track, hockey, swimming, skeet
10. football, track, hockey, swimming, skeet
Here are 3 examples I found, but I do not even know where to insert them -pressing Cltrl+Shift+Enter does not do anything.
=IFERROR(IF(or(SEARCH("foottball",E2)>0,SEARCH("basketball",E2)>0,SEARCH("track",E2)>0)),"Yes",""),""),
=IF(COUNT(SEARCH(KeywordTable,E2)),"match","no match")
=SEARCH((A1:A10=”football”))+SEARCH((A1:A10=”basketball”))+SEARCH((A1:A10=”track”))
View 5 Replies
View Related
Feb 28, 2013
how to Chk the text string in particular cell, compare it with a super set column and get the full from of the text string from another corresponsing column and the output will be corresponsing full form of the chked text string?
View 6 Replies
View Related
Oct 28, 2013
I have some 100k+ cells with values as "ID_code - Value_1 - Value_2 - Value_3". Lets say this is sequence A.
"ID_code" is a fixed 6 digit value. "Name_1", "Name_2" and "Name_3" are variables and they also vary in length, but they never contain a sign "-". Sign "-" is only used as separator between these four values.
Now, VBA code that would rearrange all the values in a selected column from the one as listed above to a different sequence B, for instant:
Value_2 - Value_1 - ID_code - Value_3
View 3 Replies
View Related
Apr 30, 2009
I am using Windows XP and Office 2003.
I am looking to automate a process where information is sent to someone in a text format and they in turn transfer that data to an excel file. It basically looks like this (but includes a lot more info):
Service Request #: 123456
Instrument Type: New Instrument
Lot/Serial #: 123456
SR Type: Product Complaint
Service Coverage: Maintenance Agreement
The info on the left (text preceding the colon) is always the same but the values following the colon can change. The excel file has all of the text before the colon and they just enter the information into the cells. I am looking to make it so that an Excel macro (or maybe a word/outlook macro) automatically scans the text document and sends this info to the exact same cells in excel every time.
The text file is actually a message from an Oracle database and I am not going to be able to make any changes to the way the data comes in.
View 9 Replies
View Related
Feb 13, 2007
I'm looking for a macro to search for records that does not contain the word hello world anywhere(any column & any row) on a spread sheet. Then I want to delete that record. I prefer not to use the autofilter function, if possible.
View 6 Replies
View Related
Dec 16, 2013
I have an excel worksheet with about 10K rows of data in column A.
I have also another list of data, about 200 rows of data, in column G.
I need to color each cell in column A that contains, anywere in the string, any of the data strings in column G.
Example:
in column A
row 1: info@duende.com
row 2: rasko@silvester.com
row 3: supportonline@fabius.com
row 4: myhelp@friday.com
in column G:
row 1: help
row 2: info
row 3: support
I need rows 1, 3 and 4 in column A to be colored.
View 9 Replies
View Related
Mar 8, 2013
How do I use an Excel formula to find which (if any) multiple sets, each of up to 50 words, exist in a series of rows of a spreadsheet - if set A has one or more words found in a searched cell.
A positive result will return a specific value in the designated result cell. If none of the words in Set A is found in the searched cell, the formulae will repeat the test for the words in Set B, and so on.
After all 50 sets of words have been tested, the formula will move to the next cell in the searched column.
New words will be added to the sets of words continually as required.
Multiple words within sets are included in double quotes. Within each set of words there will be some n-tuples of words (i.e. 24 adjacent words) that contain one or more of the words in the set, but for which the formula will be required to return a negative result. Example: Set A = word 1, word2, word 3, "word1 word2 word3". (The words within a set could also be each entered in separate columns, as opposed to all included in a single cell.) The single column of text to be searched is about 10,000 rows.
I am wanting to use the above in a spreadsheet that contains data downloaded from a series of bank accounts to automatically allocate items of expenditure to one of 20 or so different categories of expenditure.
The formula will search the description field to find words that are used in the in the downloaded files from the various accounts to describe each transaction.
If a word describing travel expenditure (e.g. hotel, "holiday inn" but not "holiday travel") is found in the description of an expenditure item - the item cost will be allocated to the TRAVEL EXPENDITURE column, which is one of 20 or so different categories of expenditure.
Happy to consider a different solution if the task can be done better a different way.
Tried using a combination of INDEX/SEARCH/IF in Excel, but was not able to get a correct result. PS I am using Excel 2011 for Mac - which does not allow macros, so the solution needs to be entirely formula based.
View 1 Replies
View Related
Sep 4, 2009
An original text string entry appearing in an Excel cell would be:
"N7C Neuroprotective J5Z Antiviral, other M2Z Antiarthritic, other J5A Antiviral, anti-HIV"
I need to extract N7C, J5Z, M2Z and J5A from this string and list these alphanumeric values in separate cells adjacent to the original text string. The challenge is that these alphanumeric references may appear in different positions within the original string with no fixed value e.g. a "," separating them. The alphanumeric references may also be 3 or 4 characters in length and there may be different numbers of alphanumeric references in the original string.
Another example would be (very different from the first):
"T2Z Recombinant, other K5B Radio-chemoprotective J3Z Antibacterial, other D3A Vulnerary A10C Symptomatic antidiabetic K6Z Anticancer, other R8A Antiasthma B6A Septic shock treatment I1Z Immunostimulant, other S1Z Ophthalmological R8B Antiallergic, non-asthma M1A1 Anti-inflammatory"
You can see that in this further example "A10C" & "M1A1" are 4 character alphanumeric strings wheras the others feature 3 characters.
View 9 Replies
View Related
Mar 21, 2013
I'm building an inventory report based upon a daily report downloaded from a website. The downloaded report changes from day-to-day. All of the data I need remains there, but the location of the columns changes, with the exception of "BOL". I need to sort all of this data in a macro. I have most of my macro figured out, but I'm having trouble with one "sort". I need the data to be sorted FIRST by whether or not the 4th column contains "Clear" or "Dyed". I have the next two sorts already coded, based on SPOT vs Contract and then numerically by column A. But I cannot find macro coding or an excel formula (either would work) that reliably searches the 2nd column for the word "Clear". I have tried different combinations of IFERROR, FIND, SEARCH, IF, ISNUM, etc. in hopes of writing a new adjacent column containing the values "Clear" and "Dyed". I have found many combinations that work if I search only the correct cell. But when I extend the range to search through ~26 cells in one row, they all fail. Below is a (small) example of what I start with and what I want to end with. I've also included my current relevant Sorting code in my macro.
FinPro
Sup
Veh
1Dn
2Cn
2Dn
Bio
Injec
[code]....
View 5 Replies
View Related
Feb 22, 2010
Attached is the mock excel spreadsheet. I want to read "sam" from the lookup column's long text "sam is good" and then write "4" in the next column. Similarly read "white" from the long text "white is tired" and write "1". And so on,,,,, For more criteria, see box highlighted in yellow,,,,I used VLOOKUP but what am able to make it work only when there is one word "sam" in the lookup column. It returns #NA when the text is "sam is good". It should write nothing if none of the criteria is met and should keep doing until the last cell in the lookup column.
View 3 Replies
View Related
Jun 16, 2014
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.
View 1 Replies
View Related
Aug 14, 2008
I am having trouble using vlookup and offset to arrive at a solution. I have a row a 5 formula based items. For example:
C 1 2 3 4
1 C 2 3 4
1 2 C 3 4
1 2 3 C 4
1 2 3 4 C
I have been using the match function to find the location of C, but I want to output the items in the same row as C, in the order they appear if C was removed.
View 3 Replies
View Related
Mar 10, 2009
Where in this function would I put " " to return a blank field if false is returned?
=IF(D18>=1,(VLOOKUP(C18,C7:E13,2,FALSE))-(VLOOKUP(C18,C7:E13,3,FALSE))+(VLOOKUP(C18,C7:E13,3,FALSE))*D18)
View 4 Replies
View Related
Aug 12, 2014
I want to search a longer text field for a shorter string that may be within it. However, I want to do this for multiple short strings.
For instance:
PhraseHidden word
Please search the text for the hidden message: Blue
The hidden word, Green, may be at any point
And the word may be different lengths like Yellow
I have a table of the hidden words (Blue, Green, Yellow, Pink)
I know I could use search to find one word, but is there a way to look for multiple words, and return the value of which one it finds? I have many hidden words (and the list is dynamic) so I don't want to just split into multiple searches.
View 5 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
Feb 11, 2014
In column E of row 1 I have one column with a long line of text containing information in that should be separated into cells for the following fields:
Year, make, model, drive, part, name, phone, text, email, location, notes, stream, date/time, disclaimer
Here is an example of the text in column E:
Year: 2001 Make: Nasa Model: big spaceship Drive:
Automatic 2 Wheel Drive Spaceship:
4 12.7 Liter ----------------------------- Part: Hood -----------------------------
[Code] .........
The data in each field can be different lengths, and I would like to take pieces of text out of Column E, and put it in other columns in the row.
Each field has its own column, and I would like to copy the data for that field from the column containing all data to its respective column.
=MID(E1,SEARCH("Year:",E1)+5,SEARCH("Make:",E1)-SEARCH("object",E1)+5)
The formula above was inspired by an "extract text between two strings" post, but will not work.
I tried to make a formula that would extract everything between the strings "Notes:" and "Team Stream:" or for example, so I could get the result "Thank you for using our excel forum! thesmartestpeopleever.com" or "Year" and "Make" so I could get the result "2001" if using the example above. I was not able to do it.
I have attached a sample photo and an excel file.
How do I separate/extract each columns data to the appropriate column?
Attached Image : excel string pic.jpg
Attached File : excel string formula conundrum.xlsx
View 2 Replies
View Related
Nov 19, 2009
I have been given a huge membership list. The field for the CITY also as the two letter state abbreviation (e.g., "Fremont, CA" instead of just "Fremont".). I want to be able to have the "CA" or "WA" or "NV" (etc) from the city field appear in a new STATE field. I successfully use the below statement to do this with the "CA" but I want a statement that will search for multiple strings (the other states).
Here is what works now: =IF(FIND(" CA",F2),"CA"). But I want to be able to add other state abbreviations to this.
View 4 Replies
View Related
Aug 20, 2013
I am trying to concatenate multiple strings of text where each may have composite or compound formatting. Is there any way to maintain the formatting? The Characters.Insert method won't work because the resulting string is greater than 255 characters. See an example below.
String 1: When you simply concatenate
String 2: these strings, Excel discards
String 3: formatting at the character level.
Required Result: When you simply concatenate these strings, Excel discards formatting at the character level.
View 5 Replies
View Related
Aug 4, 2014
I need to compare column A of Original.test worksheet to column A of Supplier.test worksheet. If there is a match then copy entire row of the corresponding match from Original.test to Matched worksheet. If no match then copy that row into OnlyInOriginal worksheet.
I have included the workbook and what the output should be. Hope it makes sense. ozgrid.xlsx
View 5 Replies
View Related
Feb 9, 2012
I am trying to search text cells to return a word count within a particular row of cells and I am currently using the following formula:
=COUNTIF($D4669:$EI4669,$O$3), where cell o3 contains the word to search and $D4669:$EI4669 the data.
However, this formula misses data that contains characters such as "," etc.
View 9 Replies
View Related
