Search Multiple Strings And Rearrange Cell Value
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
ADVERTISEMENT
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 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
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
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
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
Jan 4, 2014
String Multiples for 3 different criteria in same cell -- "if", "or" "abs" --- not sure if any of these are correct
1) Criteria 1 and 2: In cell "E" I am wanting to minus "B from C", if "A" is greater than or equal to and less than "=>1000 or <15000"
2) and the other criteria would be if "A" is greater than or equal to ">=15000" then minus "b from d" -- answer would also be in the same cell "e".
I hope I am saying my needs correctly. Basically, I have 3 different sales criteria with 3 different bonus offers. One offer is for sales less than 10,000, another offer is for the range 10,000 to 14,999 and the last is for the range 15,000 or more. I am actually linking the values from different worksheets, however I have used generically "A, B, C, D and E".
below are the 3 separate formulas that do work on specific individual rows. However, I really need the criteria for all three variables in the same cell. Right now I am just copy/paste the appropriate formula that fits the specific value in each row.
=IF(summary!$E8>15000,SUM(F3-summary!D2),0)
=IF($E4<15000,ABS(F4-summary!D3),IF($E4>=10000,0,(F4-summary!D3)))
=IF($E14<15000,ABS(F14-summary!D13),IF($E14>=10000,0,(F14-summary!D14)))
View 6 Replies
View Related
Apr 5, 2007
I am trying to copy partial data from Column A into Columns B and C, then I want to delete Column A without affecting the results in Column B and C. Column A consists of a stock number in the format "1234-56-789-0000" (including the dashes) and is formatted as TEXT.
I'd like to paste the "1234" from Column A into Column B and the "56-789-0000" from Column A into Column C AND remove the dashes (-). I need to do this for the range (or number of rows) that is populated in Column A. This could be as many as 50,000+ rows of data. After this is done, I want to delete Column A. I tried using the LEFT and RIGHT formulas, but they are dependent on keeping Column A intact.
........... A .................. B .............. C ......
1234-56-789-0000 ..... 1234 ..... 567890000
and then eventually like this:
. A ............. B ......
1234 .... 567890000
On occasion, leading zeros in Column C have disappeared, and it's necessary it stay in a 9-digit format. There are 2 additional columns of data, but they shouldn't be affected by the above.
View 5 Replies
View Related
Feb 24, 2010
I have a worksheet containing a 'first name' column and a 'last name' column. I've put together a macro that requests a first and last name, then filters on the result, or will filter on the last name only if that is all that is entered (I know this can be done using autofilter, but let's not go there!).
So far so good, but what I want to do is add a bit of code that, when a non-existent last name is entered, displays a message stating this and exits the program rather than trying to filter on the name and displaying no records.
View 4 Replies
View Related
May 20, 2002
I wish to search for a substring within an array of strings and just return back an indication that the substring exists withing that array.
so for instance let the substring be "rang"
and let the array be
{"apple","orange","lemon"}.
I wish to put a single command line next to my substring and wish to search through that range and return a key code if it exists. And then I will repeat the same search for other substrings. For the moment being, I don't care too much about the number of occurences or the exact cell address where they occur. All I wish to know is whether they exist in that list or not.
So it looks to me it is like a SEARCH command but except on a range of text (Rather than a single text) with an array formula. I have tried MATCH but my case is not an exact match either.
View 9 Replies
View Related
Aug 21, 2008
I am using the following code to search a database of information and then display it on a different sheet.
The user types the search term into a textbox and then presses a command button to search the database.
Currently it only searches for an exact match. How can i adapt it so it searches for similar strings?
Sub SearchDatabase()
Dim rRange As Range
Dim rCell As Range
Dim ResultsOffset As Integer
Dim ResultsRange As Range
On Error Resume Next
View 9 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
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
Jun 16, 2008
I am trying to find a function that will search for a particular name in a range and return ALL rows (or, preferably, specific cells in that row) which have that name in it, onto a new sheet.
eg. Sheet 1
Fred 45m $50 6/2
Joe 30m $35 5/28
Pete 60m $60 6/4
Fred 30m $30 6/6
If this range was on sheet 1, I would like to show on sheet 2 all times and dates for Fred in consecutive rows. ie the result on sheet 2 would be: Sheet 2
Fred 45m 6/2
Fred 45m 6/6
If selecting individual columns cannot be done, then the whole row would be fine.
View 6 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
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
Jul 31, 2009
I have a column of numbers that are "dated" like this:
90421
This number represents the date: April 21 2009
Is there a way to rearrange the numbers in the cell so I can then format as a date that looks like this: 4/21/2009?
View 11 Replies
View Related
Oct 17, 2012
How do I turn cell contents 20121015 into 15/10/2012 and as a date format.
The original cell value starts out as below as a filename that contains the days date:
C:FixedHoldings Report20121015 - holdings Main.xls
I then use this to turn it into 20121015:
Code:
Range("D16").Value = Mid(Range("D16"), 25, 8)
The value will always be in a similar format but the dates will change.
View 9 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
May 13, 2009
Is there a quick Macro I could use to rearrange text in a cell. For example our organisation list all the departments in an unconsistant way and it makes finding the departments hard using A-Z. For example some department will say "Department of ...", "Dept of ...", Division of ..." or "Div of ...". (Obviously excluding the quotes).
What I wanted to know is this. If you highlight the range you wish to work with, could you use a message box to ask you what text you wish to edit and then automatically search the range and make the changes.
For example search for "Dept of " (note I would need to keep the space after of otherwise I would have leading spaces) so that "Dept of ABC" becomes: "ABC, Dept of" and say "Division of XYZ" becomes "XYZ, Division of"
View 4 Replies
View Related
Apr 12, 2014
How to determine the correct formula for this requirement?
Assuming I have 2 individual excel files and an index excel file (in reality, there are more than hundreds of file). For index excel, once the user enter part number (eg. 1 or 2), the excel will look for the part number excel file and determine vlookup function.
The only problem I have here is I can not make the index file automatically add the part number shown on most left column into the required formula (replace the part number file section).
I tried use the indirect function but this require each file to be opened, which is not possible for actual use. I am looking for a function that can work in closed worksheet.
Attached files :
index.xlsx
1.xlsx
2.xlsx
View 2 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
Aug 11, 2009
I have want to search a cell to see if it contains one of multiple names from other cells.
I have a spreadsheet which contains -
Column A, which 100 rows containing different names.
Column B contains 400 rows of text
Now i want to check of each cell in column B to see if it contains any of the names from column A.
View 9 Replies
View Related
Jul 12, 2014
I have a lookup table in which names of students and the planned session dates are available.
I need to create a chart that has Student Name and Each date of month. The chart should have value of 1 wherever a match is found in the look up table.
I am wondering if combination of searching for multiple strings (student name and date ) will work somehow. The below image explains my problem
StudentTimeTable.png
View 5 Replies
View Related
Apr 20, 2014
Is it possible to combine search results into a single cell?
On my spreadsheet I'd like to select from 4 columns and combine the result into 1 cell. I'd like to search on equipment number in column K that meets a condithion in column H, if this is a match I'd like to show the result date in column P and the duration in column R. Columns P & R shown in 1 cell...
Sort of like this... if "K" = "CV537" and "H" = "0" then show the resulting date from "N" and duration from "R" in one cell somewhere.
Possible?
View 6 Replies
View Related
Oct 11, 2011
Im am trying to create a search marco button that allows me to search in multiple worksheets in one work book. I came across this CODE the first part of it works. It pops open user input box and ask for the word that i would like to search but the this error message pops up Runtime error1004 Method 'range" of object'_Global'failed and i dont know what to do
Private Sub SearchButton_Click()
SearchString = InputBox("Enter Search String", "Search")
If SearchString = "" Then Exit Sub
For Each c In Range(myRange)
If InStr(LCase(CStr(c)), LCase(SearchString)) Then
[Code]....
View 1 Replies
View Related
Nov 2, 2009
I have a workbook with many many sheets in it. The first sheet contains a single column with about 10,000 different values. I'd like to use each of these as search criteria against ALL data in the other sheets (of which there are a good 50 or so). If matches are found (they don't have to be exact case), then I'd like two things to happen:
1. The rows containing the matched search criteria in the first sheet are highlighted.
2. In the cells adjacent to the search criteria in the first sheet, hyperlinks to the matched data are created and named after the sheet upon which this matched data appears.
I've attached a sample file to this post with ideal sample 'answers' to queries made of the first 2 terms.
View 3 Replies
View Related
Jun 20, 2007
I have text (eg. 1-* or 10-* or 11-*) being entered in the following columns
Q21:Q128
R21:R128
V21:V128
W21:W128
AA21:AA128
AB21:AB128
AF21:AF128
AG21:AG128
AK21:AK128
AL21:AL128
AP21:AP128
AQ21:AQ128
AU21:AU128
AV21:AV128
AZ21:AZ128
BA21:BA128
BE21:BE128
BF21:BF128
BJ21:BJ128
BK21:BK128
I would like Excel to search the above columns provide the cell location (eg. 1-10-*) is located in cell BF26
View 9 Replies
View Related
May 20, 2008
I have three parameters that users on my spreadsheet will be using. They will be searching using the 'Brand', 'MPAN' and 'Tariff' fields.I would like to have drop down menu's for then to select the correct search option. I then have another sheet within the same workbook with all the data in it with columns headed of Brand', 'MPAN' and 'Tariff'. It has to be an excat match using those three parameters though. I would like the parameters the user selects on the first sheet to be searched and found on the second sheet, with the figures in the cells next to the search result getting fed back into a set cells back on the first page.
View 9 Replies
View Related
