Search For Text Within Cell Then Return Contents Of That Cell?
Jun 19, 2014
So right now I have a spreadsheet that looks something like this:
A
B
C
D
[Code]....
As you can see, the stock names are slightly different in columns A & C (CORP. vs CORP, CO vs CO., etc). I need a formula in column D that searches column A for the first word in column C, and then retrieves the contents of that cell. I want to do this because I will then use a vlookup in column E to get the ticker for the stock.
Right now I have: =IF(ISERROR(SEARCH(LEFT(C1,FIND(" ",C1)),A:A,1)),A:A,"")
This formula searches column A for EXXON, but does not return the contents of the cell. Instead, it returns the contents of a different cell in the column.
View 4 Replies
ADVERTISEMENT
Jun 7, 2007
Search a worksheet for a user defined text string, and have excell return the contents of a predetermined column in the same row in which the text string was found.
A prepopulated worksheet has the text "gold" entered in cell T278.
1. user searches for "yellow_metal"
2. Excell finds "yellow_metal" in row 278, say in cell A278.
3. Excell then goes to predetermined column (programed as part of macro or VB), say "T", and returns the text contents of the cell in that column, T278 in this example.
4. Excell returns "gold"
View 9 Replies
View Related
Jan 2, 2014
Below is a small sample of Column A from a spreadsheet I'm working with. All cells in the column begin with a 6 digit number:
115383_BOSTON
115384_NEW_YORK
115385_PORTLAND
115402_LOS_ANGELES
115403_WACO
115010_SAINT_PAUL
115011_SAN_DIEGO
115130_WEST_NYACK
115021_CHAPEL_HILL
What formula would allow me to enter only a 6 digit number and return the entire cell contents, i.e, search for 115403 and get a return of 115403_WACO ? I have tried INDEX and MATCH, but I'm not getting the results I need.
View 7 Replies
View Related
Dec 21, 2008
I am trying to write a formula that will return a statement if a certain month is contained in the text within another cell. Formula is =IF(ISERROR(SEARCH("Dec",Assumptions!B2)),"Ensure Journal is Non Reversing","")
Cell B2 contains a date in the format of Dec 08, so if this date contains Dec, then return "Ensure Journal is Non Reversing", if it doesn't then leave the cell blank.
At the moment it is putting in the first test for every month I select and not changing to blank.
View 2 Replies
View Related
Mar 12, 2014
I thought this was a pretty simple formula but I am having difficulty creating it. I am attaching a little test spreadsheet. Sheet 1 is where the data will be entered. The Reimbursed column has a drop down choice of yes or no. The next 2 columns are the cost of registration and the cost of accommodations. On sheet 2 is where I would like the formulas. So in cell A4 I would like a formula that says if B3 on sheet 1 is Yes populate this cell with the contents of Cell C3 only, B4 of sheet 2 would then be B3 if A3 on sheet 3 is Yes and so on with the Not reinbursed if sheet 1 the Reimbursed column is no.
View 3 Replies
View Related
Aug 20, 2013
I have a large sheet (approx 4k rows) and need to verify the contents of each cell by taking the name and searching for it in another workbook. I then need to copy over the cell contents found under certain columns in the secondary book and paste them into appropriate columns (row where search name was found).
Ex. I search for Apple and find 6 instances of Apple in the second book. I then take the contents of the cells found under columns Make, Model, Year (all from the row that showed the results as apple) and paste that into the same-named column in the first book.
I have been doing this manually but it takes too much time.
View 2 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
Mar 7, 2014
how would I go about having a button on my worksheet which is clicked will open up Internet Explorer and search the information located in a specific cell?
View 4 Replies
View Related
Feb 24, 2014
My current problem is that I need to search two tabs of data and use the results to modify the contents of one cell in the first tab.
I'll try to be as clear as possible:
tab1 has relevant cells "a" and "b", same row
tab2 has 3 relevant cells "c", "d", and "e", all on the same row
All cells are text values - some digits, but should be treated as a regular String.
The search needs to look at cells "a" and "b", find their match in "c" and "d", then replace the original value of "b" with the value in "e" after a match has been found.
View 1 Replies
View Related
May 2, 2008
I have created a desk planning spreadsheet in excel. Each week a mailmerge sends out an email to everyone in the office asking them to confirm when they will need a desk in the next week.
As the normal employee doesnt get involved in desk planning I want them simply to state whether they will be in the building or not by entering "GH" (our building) in a cell under each day in the email they get sent.
When the reply comes in we select the cells from the email and then paste them into the desk planning spreadsheet.
-----
WHAT I NEED TO DO:
Select some cells (must be in the same row - that we have just pasted in).
Press a button on the Worksheet labelled "Auto Assign Desk", which does this:-
For each cell that contains "GH", replace with the right-most cell to the left of the selection that starts "GH_"... (this is the start of a unique desk reference (GH_1_1, GH_1_2,etc.)
-----
This will assign the employee to the desk they were sat at last, when they are in the office next week.
View 7 Replies
View Related
Apr 5, 2014
i have an excel file with hundreds of rows of product codes which i need to download images for. is there a way this can be done directly from excel? basically if cell A1 has product name and Cell B1 have product code, id like Cell C1 to generate a link that will use the contents of B1 as the basis of my google image search. can this be created using a macro?
View 9 Replies
View Related
Feb 18, 2009
Been racking brain, searching through the forum here, and my Excel 2003 Bible all day trying to figure out this problem to no avail. I would like to clear the contents of any cell in a given range if the cell immediately to the right of is formatted as bold.
View 2 Replies
View Related
Dec 19, 2011
I am new to VBA and am having difficulties in getting a find function to successfully locate search criteria within cell. When the search criteria matches exactly that of the cell contents then the code works; however should the search criteria only form part of the total cell contents (such as a seach for "the" in a cell containing "the cat sat on the mat") the code doesn't recognise it.
Essentially, I need the code to search a range for the required string and if found within a cell activate that cell and populate a combobox with the full cell contents of the activecell.offset(0,-2).
The relevant section of code attached below:
Dim role_count as range
Dim role as string
If Application.WorksheetFunction.CountIf(Range("Role_Count"), Role) 0 Then
Range("role_count").Select
Selection.Find(What:=Role, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
Me.Controls(ComboBoxName) = ActiveCell.Offset(0, -2).Value
View 4 Replies
View Related
Jul 25, 2014
I have inlcuded my workbook that I use for tracking the NFL season and I'd like to add in some team logos just to make it look a little cooler.
The attached workbook includes three sheets, the first of which uses a drop-down for selecting picks. The second sheet has the team logos and the third sheet feeds off the first.
I'd like for team logos to populate into the third sheet based on what is selected in the first sheet.
I've already tried a few methods, all unsuccessfully. I tried this [URL] which was a great idea but it didn't allow for the same image to be repeated potentially 100 times. I also tried a method involving named ranges and indirect lookups to the linked picture image but that would require a different named range for every person and every pick which isn't feasible.
if it's possible to just do a massive nested if statement with a vlookup to return an image but I haven't been successful in that either.
View 5 Replies
View Related
Jan 7, 2012
I would like cell CM2 to look at cell G2 and get the value there. Here is where it gets interesting. I would like it to reference sheet "LookupTables" in column A.
Cell G2 will be a 4 digit number.
Cells in column A of sheet "LookupTables" will be formatted like this: 3 digit number-4 digit number-4 digit number, like this "001-0001-0001".
If cell G2 exists in column A of the LookupTables sheet, (but it needs to ignore the first 4 digits and the last 5 digits of that number) then it should return that value in cell CM2. I would like it to look through the entire column A of the LookupTables sheet and return all numbers that match the above criteria, and separate them with a comma.
So, if G2 is 0001 and column A of the LookupTables sheet contains several values including:
001-0001-0006
004-0001-0187
098-0001-0874
098-0002-4356 (it should ignore this since the middle numbers don't match the value of G2)
then CM2 should display: 001-0001-0006,004-0001-0187,089-0001-0874
Also, if there are no matches, then it should just return a blank cell.
View 3 Replies
View Related
May 7, 2014
I am trying to figure out how to return the contents of last non blank cell in column B, based on the name in column A. So, if I choose "Sam", the result I am looking for is "blue". If I choose "Pete", the result I am looking for is "orange".
Sam
red
Sam
blue
Sam
Pete
orange
Sam
View 4 Replies
View Related
Nov 5, 2009
In Column A, in each cell in rows 1-10, there is various text that may or may not include "ABC" and "DEFG" as the first four letters. In Column B, rows 1-10, there is numerical data. What I would like to do in Column C, rows 1-10, is place a formula that would return the contents of the respective cell in Column B, but only where the first three or four letters in the respective cell in column A is "ABC" or "DEFG".
View 9 Replies
View Related
Sep 3, 2006
On Sheet2 I have a table where I want M2 to do the following:
1.) Look at cell A2 where the name "John" is and B2 where the name "smith" is.
2.) Look at sheet1 and look in B2 and C2 for "John" and "smith"
3.) Once it matches the name, place the contents of sheet1, cell I2 into sheet2, cell M2.
So in short, match the name on sheet2 to the name on sheet1 and return the number in I2 to M2 on sheet2.
Maybe combining the persons name in to one column would make it easier?
View 9 Replies
View Related
May 9, 2014
ie:
Cell A1 = Ref#01
Cell B1 = A1&(whatever I choose to type)
Result in cell B1: Ref#01whatever I choose to type
Looks like I would need a macro because a function will be overwritten as soon as I start typing in cell B1.
View 12 Replies
View Related
Feb 9, 2009
I want to display the contents of cell B5 in A7 by using cell C7 to show the location of the cell to be displayed. Cell C7 has a formula that shows the cell location of information (text) that needs to be displayed. How can I show in A7 the contents of cell reference shown in C7? In figure attached I show an example to better understand. Want in A7 to show the contents of B5 by reading what is in C7. So A7 cell should show word Road.
View 3 Replies
View Related
Jul 3, 2009
to search for specific text in a cell but not just one word or letter, in fact it could be many. I need to first verify that the exact texts exist in a table and then match the various text instances with corresponding data in that table.
At the moment, this works to search for one type of text and verify it's exsistence in the able and indeed return the corresponding value, but i need it to do it for all verified texts
=(INDEX(A2:A4,MATCH( LOOKUP(9.99999999999999E+307,SEARCH(" "&$B$2:$B$6&" "," "&D2&" "),$B$2:$B$6),B2:B4,0),1))
Example attached
View 9 Replies
View Related
Jan 28, 2014
I have 2 tabs in a 2013 workbook. Inventory Receipts and lookups. One of the Data verification lookups I have is a drop down list in each cell in Column B (eg: Cat, Dog, Mouse) In the lookups tab I have another cell range containing the sounds (eg: Meow, Bark, Squeak).
What I am trying to acheive is, if B2 contains Cat, then return Meow in B3, if B2 contains Dog then return bark in b3 and if B2 contains Mouse return squeak in B3. Ideally I would like the formula to return the text from my lookup sheet (eg: 'Lookups'!C2,'Lookups'!C3,'Lookups'!C4). So depending on which option they choose from my animal drop down list .. the correct noise would automatically fill in.
View 9 Replies
View Related
Jun 18, 2014
I am trying to search a sheet for a given name and return the total point for that marking period. The main problem i am having is that the names aren't in the same cell on each sheet. It is a list of students and each sheet represents a marking period.
I have attached the file
View 3 Replies
View Related
Jun 25, 2014
I have a formula which I took from another post to return a particular value in a specified cell as follows;
=INDEX(A1:E9,M3+MIN(IF(A1:E9=K3,ROW(A1:E9)-3)),N3+MIN(IF(A1:E9=K3,COLUMN(A1:E9)-1)))
However I don't understand what the -3 and -1 does and I believe it is these entries that are causing the error.
See attached file : TEST.xlsx
View 13 Replies
View Related
Dec 8, 2009
If I have a 'key' value in a cell in one sheet, i want to use that value to find the cell in another sheet containing the 'key' and return the row number of the cell, if more than one value then I would like to be able to loop through all the rows containing that 'key' value returning the row number of each hit, kind of a programmatic version of vlookup?
View 8 Replies
View Related
Jul 31, 2013
I need a formula that will search for the value of C1, Sheet1 in Sheet2 then if found, return a value from that row in column D? If not found then return a message "search emails".
I can upload a dummy if that's possible?
View 5 Replies
View Related
Jun 9, 2014
Want to look in one column and find the first non-blank numerical value, then have it return a value from another column.
Used to nest multiple IF functions together from different cells, but it seems overly complicated and time consuming. Sometimes I have over 30 cells to check.
For example, if Column A contains weekly sales data by week, entered weekly, and Column E has corresponding comparison data from the previous year. I want to enter a formula to check the first row that has sales data entered and have it match up the comparison value in the other column.
View 7 Replies
View Related
Mar 6, 2014
i wish to have a result of the below.
there are random dates in a column say D6:D17 in the format of (10/02/2014), there could only be one date in this column array or there could be several, like:
1/02/2014
12/04/2014
17/06/2014
5/12/2014
i also have an aray below this in cells D19:D30 With the "A" column showing the months Jan to Dec.
i wish to look the array of D6:D17 and populate the below cells corresponding with the months only, see example below. the reason for this is i wish to then turn the cell green with conditional formatting if there is a date in that month fro the original array.
Date10/02/2014
Date10/04/2014
Date10/06/2014
Date10/08/2014
Date10/12/2014
[Code]....
View 6 Replies
View Related
Mar 10, 2004
I need a macro to automatically copy the contents of a cell to a text box.
View 7 Replies
View Related
Jan 22, 2014
I need to lookup to search a range on a different sheet and return the appropriate corresponding cell.
Basically its if a1 is found in the other sheets range a1:a100 then return the corresponding Bcolumn value from the different sheet.
Formula
=VLOOKUP(A2,'All Users'!A$2:A$100,'All Users'!B!2:B!100)
Example of 'All Users' Sheet
A B
STAFFIDSTAFFNAME
24177John
10487Paul
20031George
84087Ringo
85772Pete
24485Stuart
3829George
51836Yoko
View 3 Replies
View Related
