Search For Multiple Text Strings In A Cell - Return One Of Multiple Corresponding But Different String
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
ADVERTISEMENT
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
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
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
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
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
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
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
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
Sep 24, 2013
I have 2 columns ,A and B, in a table and text strings in C. Each table row A1 and B1 needs to be compared and matched with the text string C:C. Only if both, A1 and B1, are found in the same string, say C5, it has to return A1&B1. If not, return "Not Found".
Several Obsticles:
1. A and B are text and number
2. C is text with different numbers saved as text and words all mixed up
3. There is no defined format for the text, it is different in each string
A
B
C
-500.000,00
Banana Apple
Data: 1 MANGOGrape 0000000 800,000.00 EUR something something
-800.000,00
Mango Grape
Something your Ref: 8750ours R ef: TG0PPM000000743 500,000.00EUR zu Gunsten Banana Appl e VVA
With all these different formats and inconsistency, is it even possible?
View 2 Replies
View Related
Sep 24, 2010
I have a workbook that contains 18 sheets of data. These worksheets are named according to their respective client names (AAA Shine, Fern Barn, Oracle, etc. for example). On each of these sheets, is a column (Column B), that has a salesperson's name in it (Fred, Joe, Ted, Anne... there are about 10 names total). On each sheet, there are corresponding values for the revenue from that account for each month of the year. Those values appear in Column L (Jan), M(Feb), N(Mar),... W(Dec).
What I would like to have is formula that will look at each sheet, find all the instances of Fred for January, sum them, and report them in a single cell on a different sheet I have called "Summary". The end result will be a total revenue from all customers, for each sales person, by month, in one summary sheet.
View 9 Replies
View Related
May 12, 2012
I have a dataset of shifts and want to compare each shift that needs to be filled to a list of requests for time off (vacation, etc).
For one cell the code is: =IF(ISERROR(SEARCH(B1, A1), 0,1)
Where A1 is the column of requests in comma delimited forme (ie: "AB,CD,EF").
Where B1 is the column containing the person assigned to shift 1 (ie: "AB")
In this case, would return a "1" as no error was returned, as AB was found in the list. Here "1" would represent a schedule conflict.
Without creating many cells for each shift- there are 20 shifts- can I create an array or string together this type of "SEARCH" function?
View 1 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
Sep 30, 2009
I have a spreadsheet!
I have two sheets, one of which contains film names and the other contains our tag structure for our website (which is a list of tags, their keywords and the primary parent channel they live under).
What I am trying to do is search the film titles and if any of the words match either the tag name or keywords then return the relevant channel.
Example:
Sheet 1 - Films
How To Apply Bridal Makeup
How To Fight A Donkey
How To Write Excel Formulae
Sheet 2 - Tags
Channel / Tag / Keywords
Tech - Microsoft - windows vista xp word excel
Tech - Computers - internet pc
Tech - MP3 Players - iPod Zune
So, for film 3 on Sheet 1 it would recognise the word Excel in the keyword list and return the channel Tech.
View 12 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
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
Jul 30, 2009
I am trying to create a formula to display a string of text that refers to multiple cells. for example, =If(A1=0,"insert text here" &B1 "insert more text here" &B2 "insert even more text here" &B3, "insert text here" &C1 "Insert text here").
It works fine for one cell value, like ="Total: " &A1
View 8 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
Apr 17, 2013
I require the code for the following task.
I'm getting an input string from the user in an excel sheet(e.g. input.xls) .I have to search for that string in a group of excel files present in the same folder in which the input.xls file also present.The range of that cell in which the string is found (e.g. A1 ) has to be stored in a variable.
I know that Vlookup is an option but the number of excel files to be searched is more that 50.
View 1 Replies
View Related
Aug 1, 2013
I have 5 sheets in my workbook and I need to find out if a string exists in any of them. The string would always be in column L of the work sheet. I have searched many places but cant seem to get anything to work, is this possible?
View 8 Replies
View Related
Jan 22, 2014
I am busy building a spreadsheet that must do the following.
A
B
C
D
E
[Code].....
Above is a basic example of the data I am using What the spreadsheet must do is it must search cell B1 for the key word in in cell E1, if it does not find the keyword, it must search the same cell for the keyword in cell E2 and so on if it finds the key word it must return a consatination of cell A1 and the Keyword.
This is easy enough to do using the search function together with a couple of nested IF statements but where the problem comes in is that I am needing to run the search on about 12000 lines using over 10000 key words.
currently I have sheet with a formula in Column A containing 40 nested IFs searching for the first 40 key the same formula in Column B searching for the next 40 keywords and so on but to search 10 000 keywords(Client name or Client number) I am needing to apply this formula to 250 columns over 12000 line which makes the sheet very resource incentive.
How do I streamline this to make the spreadsheet more efficient
View 2 Replies
View Related
Apr 22, 2013
I'm trying to build a formula that counts the amount of cells within a range that contain multiple strings of text within the same cell. I only know how to build a formula that snags cells that contain 1 but not 2 different ones within the same cell. For example: I want to count cells if they have the word BALL and STICK somewhere in the cell....see three cell examples below
gameballnetstick
ballgame
stick ball
tenballs
green stick
Of the three examples: it would only count cells: gameballnetstick and stickball
View 2 Replies
View Related
Jun 11, 2014
In Column A I have the list of strings that I need to search. I want to count the number of occurrences of these strings inside these two text files: Sample1.txt and Sample2.txt. For example I have the string "DOG". I want to search and count the number of times this string appeared in Sample1.txt, and Sample2.txt
See below:
View 5 Replies
View Related
Apr 28, 2012
I am having such a difficult time creating a macro that will reduce the 5+hours I have to spend each week manually copying & pasting all of this data. I making an IMMENSE difference in this worker bee's life!
I have a workbook with two sheets (Sheet1 & Sheet2). Sheet1 has license #'s in column A and the state that the license belongs to in column B like this:
COLUMN ACOLUMN B11111Alaska11112Alabama11113Arkansas11114Arkansas
Sheet2 has three columns. Column A has the license #'s, column B has the state that the license belongs to and Columns C shows a line-of-authority tied to that license #.
COLUMN ACOLUMN BCOLUMN
C11111AlaskaProperty11111AlaskaCasualty11112AlaskaLife11112AlaskaHealth11112
AlabamaProperty11112AlabamaCasualty11113ArkansasLife11113ArkansasHealth11114
ArkansasLife11114ArkansasHealth12345ArizonaProperty
I'm trying to write a macro that will compare the license # and state in Sheet1 to the license # and state in Sheet2. If it matches, append the contents of Column C to the corresponding row in Sheet1.
Here's the thing...Sheet2 contains the entries for all licenses in the company (so this table is HUGE). And there are multiple entries for each state license # (notice how there's two entries above for AK license # 11111 - one for the Property line and one for the Casualty line.
After my macro is run, I want Sheet1 to show all the lines-of-authority on a single line. So if I ran my macro on the above example, after it's run I would have this in Sheet1:
COLUMN ACOLUMN BCOLUMN
C11111AlaskaProperty Casualty11112AlabamaProperty Casualty11113
ArkansasLife Health11114ArkansasLife Health
View 5 Replies
View Related
Sep 21, 2007
I have a bunch of quotes in Excel format which have 5 columns ; Part number, Description, Qty, Cost, Total Cost, Unit List,Total List. I need a piece of code to open many excel files, look for a "part number" beginning with ED,DS,AP,MP or MDS and return the corresponding value in the "total cost" column then record the value in a separate spreadsheet.
View 2 Replies
View Related
May 22, 2009
I am trying to search for multiple values in a cell with Multiple criteria.
E.g: Please find attached the sample excel data.
I tried using VLOOKUP which is not working as I am having multiple values in column A.
View 9 Replies
View Related
Jun 9, 2014
I'd like to extract the data from Sheet 2 (Data) that falls within the selected date range but the formula I've entered in F$9 (see below) is giving me an error
=IF(ROWS(A$9:A9)>$A$5,"",INDEX(Data!A$2:A$387,SMALL(IF((Data!$A$2:$A$320>=$A$2)*(Data!$A$2:$A$320<=$B$2)*(Data!$B$2:$B$320=$C$2),ROW(Data!$A$2:$A$334)-ROW(Data!$A$2)+1),ROWS(A$9:A9))))
test1.xlsx
View 3 Replies
View Related
