Searching A Cell For Mulitiple Strings And Returning A Value
Sep 12, 2008
I am trying to search a cell for multiple different strings and if the string is found, multiply a nearby cell by a factor of 1.5. if the strings are not found i want the original cell value to be used. I have tried using find functions but they do not seem to work. I have also tried using nested if functions with no luck..
Our software extract for our data base sucks and values do not align properly if all entries do not have tge exact same number of fields with data in them.
I have always done the alignment in excel manually. I can find where the data starts to misalign and would like to do this automatically.
There is an id number for each entry (row) that is 6 digits long. How can I search a row for a 6 digit number and return the address of that cell? i can use this address as a starting point and use the offset function to do so alignment from there.
I need a formula (but most likely a VBA macro) that will search through a folder for a file than get data from that file. The files are named in systematic way, but I need the entire formula to work from inputting a mold number in one cell. E.g. I input 6291 in cell A2 the vba macro searches for file “6291 mold.xlsx” and returns a range of numbers as well as pictures in specified cells. Is this possible? If so how?
The closest thing I have found is VBA macro that retrieves a list of media files in a folder, I listed the code below.
I am trying to parse stock holdings for certain companies but I am running into trouble because they are not uniform in their form. The data has a general form but each company uses a slightly different style for the forms. These differences are hamstringing my efforts to effectively parse the data. I have attempted to parse the data using the split function to get it into a more manageable form, and then use various comparisons to tease out the right information. This, however, has not panned out like I hoped. Below are some sample data forms and some of my (albeit poorly written and structured) VBA to try and solve the problem.........
I am trying to extract the same fields from each, such as name or number of shares:
3COM CORP 885535104 1,700,000 Prudential Financial, Inc. 432848109 100,000 this would be a desired output.
here is some of my code to try and accomplish this. this seems to work the data if it is formatted correctly, but I would like write a general parser, and this is what I am struggling with. I cannot figure out how to write something that will correclty parse these irregular strings.
txt = ActiveCell.Value x = Split(txt, " ") name = "" cusip = "" shrs = "".....................
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.
What I would like to have done is to look for each cell in column M with only the string of "note" in it, then look one cell below and to search the text using boolean operators and if certain words are found, increment a series of counters.
For example, using cells M16 and M17 as references, if "please" AND "relationship" AND "customer" are found within the text, create a counter and increment it by 1. The program should loop through all of the cells in column M and determine how many instances of that particular set of words is found. If there are 20 instances in the column then somewhere on the sheet, counter 1 should indicate 20.
There are a couple other set of words I would like to search as well, with each new set creating a new counter and indicating the total instances found of that particular set of words. For example, in cells M40 and M41 I would like to search for "failing" AND "name" AND ("first" OR "last"). Thus, if there were 50 of these instances a second counter somewhere on the sheet would indicate the number 50 when the program finishes.
I was using the find and replace but I don't think I can use boolean operators with it, and I'd also like to learn how to do this programmatically.
field B7 b7 ; field a8 @latestdate("PCP2EHSEAAAAh", jEnd); field b8 @if(a8 <jStart, @latest("PCP2EHSEAAAAh", jEnd), @avg("PCP2EHSEAAAAh", jStart, jEnd)); --------------------------------------------------------
I want to look through this text and copy out any 13 character codes that are present (e.g. "PCPACIMTAAABl" ,2 "PCP2EHSEAAAAh").
These codes all share the following characteristics,
1) they are all 13 characters in length 2) the last character in the code is always either a "l", "h" or a "c". 3) they contrain no spaces 4) the first 12 characters are always in CAPS (followed by a lower "l", "h" or a "c".
I receive a spreadsheet with over 400 lines of data every month. One row (J) contains information from companies with account details. The problem is that the info uses different formats, so the data strings within the cells can be quite long (sometimes over 100 characters, including spaces, dashes and slashes). The one identifier within each data string is a name, usually somewhere in the middle.
So for example a reference to JOHN SMITH might be "123456-01-smith/john-01/01/10-London-Edinburgh" or "2010-01-01-123456/SMITHJOHNMR/123456". I have a list of names (Column Y) and what I need is some way (probably VBA) for Excel to look down the list of references and compare them to the list of names, and if one of the names appears anywhere with in a text string, to put the name in column K. So if 4546/JohnSmith-01/08/13 or Leeds-London-123SmithJohn789 appears, put John Smith in column K, if 0123-MrFredBlogs-0321-5 appears, put Fred Bolggs in column K.
I have created a worksheet with 7 Tabs. 6 of the tabs are tables of existing parts and their catalog information. The Front Sheet I want to be a place where people can select data via a drop down or typed in, and in turn have that headsheet display the results of the search.
I have the 6 tabs broken down by part type, and figured those can easily enough be the first choice on my worksheet. So by picking that I can direct excel to which tab we want to run the search on, but ater that I'm drawing a blank.
For an example, say I'm looking for a Elbow that has a 6 size on one end. I would like to use the drop down to choose "Elbows" and then like to select/type 6 in the End 1 size box and have Excel show me all the reslts from the Elbow Tab that match that criteria.
I have been trying to find a formula for this but I have yet to find one that works for me.
Data;
English - United Kingdom, Czech, Spanish UK - United Kingdom Swedish English - US French US France German Portuguese +8000 more various values
I am looking for a formula that can search for multiple values e.g. english, us, uk and united and then return the value "Yes".
I was looking for something that includes wildcard searches or contains so that it is not affected by upper or lower case but combining this with multiple searches was proving difficult.
I have some code traversing a directory of folders and subfolders on a shared server and returning a list of those file paths. Around 6000 folders with about 2 subfolders on average. This returns a list of 12000 filepaths but takes around 13-15 minutes to do. Each path looks something like this:
My Excel 2010 spreadsheet contains client data like the below:
A B C D E
What I'm trying to get from this is a personally addressed email with 2 attachments, one will be standard to all recipients and one will be unique and specific to that recipient. The filename of the unique attachment will contain the reference but will have some other stuff in the filename as well on either side that I will not be able to remove.
In terms of the file locations the unique ones will be in subfolders of the folder holding this workbook and the generic one will be in the same folder as the this workbook.This is some adapted code that solved a similar problem (in Excel 2007 though) on a different website.
VB: Sub Mail_Report() Dim OutApp As Object Dim OutMail As Object [code]....
In that case the file started with the "reference" field but in my case it is in the middle and the formats vary depending on the provider, there will only be a maximum of 5 providers but I would like to avoid 5 different macros if at all possible so I need a search function of some sort.
I was wondering if the data validation works in terms of duplicates across multiple tabs. I.E If the user enters the Unique ID in column A on Sheet1 but the SSN is already entered in column A on Sheet2, but I just wanted a warning to Appear letting the user know it exists elsewhere in the file. I assume I would have to do this in vba but not really sure how I could start it.
These cells contain a time reference either in '09:30 - 16:30' format or just an 'AM/PM' format.
What I need to do is to populate column B with the part of the string that comes AFTER the times in the text (either in hh:mm format or AM/PM format)
Example 336 Data 2012 Classroom Induction 2012/08/28 09:30 - 16:30 data about this ------> data about this 336 Information about this 2013/04/04 09:00 - 13:00 Information 2 ------> Information 2 336 info CHS Suffolk 2013/05/07 PM DRC, BSE ------> DRC, BSE 364 information 2013/02/12 AM DRC, BSE ------> DRC, BSE
I cannot get this to work and cannot use Text To column as there is no character I can use to split it.
I want to test a cell for the type of value as 678/256 or 345/872/098/987 etc. The common character in each would be the "/". First, I need to check the cell for this, then take the first number and compare it to an integer(if it is <> , or = to). I hope I am clear with my situation.
I'd like to search a cell A1 containing for example 'hello_L765'. If the 7th character is L and the 8th character is text(ie not a number) than display yes, otherwise display the 7th character.
Not the most exciting data, but I need to identify which cells contain the sub string "HWA". Ideally the next column would have that string extracted into it or some kind of indentifier
I'm trying to search a column that has cells with various length strings. Included in the strange (at random locations) could be possibly be a 7 digit number starting with 4. I want a formula that counts the number of cells with contain that 7 digit number. Is this possible?
I have a cell that is populated by clicking a button which pulls info from an external source over which I have no control. I need to confirm the selection made by the user contains one of four quarterly values: Mar, Jun, Sep or Dec. I have this but wanted to know if there is a better alternative or a way to shorten the code.
Code: If InStr(1, UCase(celltxt), "MAR") Or InStr(1, UCase(celltxt), "JUN") _ Or InStr(1, UCase(celltxt), "SEP") Or InStr(1, UCase(celltxt), "DEC") Then
I need to extract the number (regardless of the amount of characters) signified by the "." Another words, I need a formula that will give the the answers of the following in column "B"
1540.12 37.95 86.37
These are the amounts spent. It would be easier if there was a "$" but theres not... so I need to find the "." and give me the numbers associated with it.
I'm using a command button (p1) within Private Sub P1_Click()
It pulls the activecell as a string (for example $A$1) using a variable called CurrCell, I then want to search this string (currCell) within the worksheet "DATA" and select the cell 3 to the right of it, so that I can change the value.