Search Partial String, Return Value In Next Column, Vba
I would like to search cells in column D for the partial string, "PIPE," (A full string may look like this: 'PIPE, 24"ODx0.375"WT API-5LX-65,ERW OR SMLS'). Then, if it's there, return the value "LF" in the corresponding cell in column C. If that string isn't found, then I'd like it to return "EA".
I know this seems pretty easy, but there's a small problem. The word "BENT PIPE," could be in Column D, in which case, I would want it to return "EA" instead of "LF".
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Lookup Partial String To Return Data From Corresponding Column
I have a simple lists containing two columns. One column contains a five digit number and the other a vendor name. The vendor name in most cases is two to four words. I am wanting to type in a partial string of the vendor name and it return to me the 5 digit 'vendor' number. Col A Col B 20567 3M Electrical, Inc. I want to type in '3m' or '3M' or 'electrical' or 'ELECTRI' and it return the 20567. The other part of this is that there may be two rows with the same info in which I would need to see both... Col A Col B 20567 3M Electrical, Inc. 21789 3M Tape Division Is this possible with standard lookup features in Excel or does someone have a VbScript or macro that will accomplish this?
View Replies!
View Related
Retrieve Record On Partial String Search
I have created a Userform for entry data's in a Excel2003 file. I would like to retrieve a complete record (= a row) by searching on a string contained in a cell of this searched record-row. Here an example of a row and I'm searching upon "2041" via an inputbox. idn° claims Dateréf. Suppl supplier Fax n° PO 1AAA2041 2/01/08200039 BOSCH (RAS) 056/20.26.75 774634
View Replies!
View Related
Partial String Check In Cell Against Range For Return
I've posted this query before, not on this forum, but I don't think the replies I've had so far are going to do what I want. Initially I was looking for a formula, but the suggested pile of nested IFs won't work for the number of conditions. I saw a previous post on here for a VBA macro to search for a text value in a cell against the cell contents of a range and it seemed to do at least the first part of what I wanted. I attempted to manipulate it a little to test its applicability for my own nefarious purposes but for the life of me I can't get it working. This is complicated by the fact that the actual data is commercially confidential, so I can't show you the actual file, but I can fake what I want with two simpler ones. I've attached them to this post. What I want is a fair bit more complex than the other post I found - I want to be able to compare a partial text string from a given cell in a range ('Check Value' in the attached TestBook2 ) against the strings in a range of cells ('Value 1' in TestBook1), and return the corresponding value from 'Test Value' to the corresponding adjacent cell to the tested 'Check Value', with an order of precedence, for example... Testbook2 contains an entry in C5 of 'a, e, h, z, x, y'. Testbook1 shows that the return for a, b, c, or d is 'moo', for e, f, or g is 'steve' and for g through q is 'fred', all others being no returned value. Moo>steve>fred, so I want the corresponding 'moo, steve. fred or <blank>' cell to contain 'moo'. Conversely, C6 contains 't, u, z' and therefore shouldn't have a value in 'moo, steve, fred or <blank>'. C12 contains 'f, z, s, y, u' and C15 'i, x, z, s', and therefore should display 'steve' and 'fred' respectively.
View Replies!
View Related
Partial Text Lookup In Text String And Return
Cell H1 has a variable string of references for eg "FI570783AQ3516346EQ3516346FXVB123456" I want to return the reference beginiing with "FXVA" so it would ignore the rest and only return FXVB123456 - this should always be 10 characters. Just to add another complication to the mix, there may be 2 "FXVA" references in the string and i want to get both (these can be in the same string so FXVB123456FXEL123456 - but this might not happen regularly.
View Replies!
View Related
Search For Text Within A String, Lookup And Then Return Item
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 Replies!
View Related
VBA - Search For A String
Scenario: A1:A2000 (or the wole column) will have the following: Black Total Grey Total Total Grey Print Blue Total Print and so on. Each cell will contain the word "Total". In B1:B2000 ( or the whole column) I would like to remove the word total from the column A. Example: B1 should only contain Black (instead of Black Total) B3 should only contain Grey Print B4 should only contain Blue Print
View Replies!
View Related
Search String In Column
I have this sheet: BEFORE MACRO.............. MACRO RESULT.............. Macro descripion: Column A ( SEARCH STRING) list the string to be searched in C column Column B (RESULT) reports the macro result Column C ( List ITEMS ) is the column where the macro have to search the string Operation: - Starting from A2 up to the end of value in A column - Search the substring ( A2 cell value ) in C column starting from C2 * if found copy all the C row content in B2 If 'not found' write NOT FOUND (*) all search ( from A2 to An ) starts always from C2
View Replies!
View Related
FindNext (search In Column For A String)
1) search column A for a string 2) as that string is found, move the entire contents of that cell to the same row, column F 3) insert a blank row above the row where the text was found 4) continue to search and perform the same steps I was able to come up with the code to find a single instance and perform the necessary steps, but I've tried 'til I'm blue in the face to incorporate the FindNext object to parse through the entire worksheet. I thought I was on the right track with the attached code (which could be completely wrong), but it returns my MsgBox "Value Could Not Be Found."
View Replies!
View Related
Nested IF(SEARCH The String In Column)
Column A contains an alphanumeric string up to 50 characters in length. I need a formula to search the string in Column A for the word "Appeal" and place that word in Column B when found. Likewise for the words "Grievance" and "Complaint". If none of the three words are found in Column A, the contents of Column B should be blank. Here's the formula I've played around with (it works on the first line but returns #VALUE! on following lines): =IF(SEARCH("Appeal",A1),"Appeal",IF(SEARCH("Grievance",A1),"Grievance",IF(SEARCH("Complaint",A1),"Co mplaint",""))) I've tried entering it as an array, but that also fails. This seems like a pretty simple problem ,but I've spent several hours trying to make the formula work so I'm obviously doing something wrong.
View Replies!
View Related
Search A Column Of Dates & Return Data From Another Column
I am trying to get a formula that will search one column range “B” and pull data from another, “D”. Dates are down column “B” and some of those dates are repeated several times. In column “D” there is only one piece of data (a number) entered for each day. Eg, if 08/06/2009 has been entered 3 times in column “A”, there will only be data entered in 1 of the cells of column “D” and blank cells in the other 2. Column B -- Column D 07/06/09 -- 54000 08/06/09 08/06/09 -- 62000 08/06/09 09/06/09 -- 61000 I couldn’t get the LOOKUP function to work properly, as there are duplicate dates in column “B” and I often got a result of 0 returned. As I’ve only got 1 piece of data added in column “D” per day and any duplicate days would just have blank cells in “D”, I can actually get a SUMIF function to work, SUMIF(B3:B60,DATE(2009,6,8),D3:D60). Although it does work, it doesn’t feel right using it and I would prefer a formula that would return just the one cell, instead of the sum of a range of cells.
View Replies!
View Related
Search Column & List All Occurences Of Value Offset From String
I need to make a list of items that occur above the string "Room" and the data offset to the bottom and bottom right. Here is the sample data: 1, 100A 2, Room, Rh 3, 123, 11 3, 200B 4, Room, Rh 5, 456, 24 6, 300C 7, Room, Rh 8, 789, 56 ... On another sheet this is what the output should look like: 1 100A 123 11 2 200B 456 24 3 300C 789 56 ... Here is a copied function that I've been trying to work with. "ROOM_AREAS" is the range in column A. I just can't seem to figure out how the ROW and SMALL functions are supposed to work here.
View Replies!
View Related
Way To Return Column String Values
I have made this function that returns the string value for a column based on it's number but it starts getting more complicated after I've been thru the alphabet once and have to return two string values to move across the chart. PHP Function colnum_string(ByVal num As Integer) As String colnum_string = "IV" If num = 1 Then colnum_string = "A" End If If num = 2 Then colnum_string = "B" End If If num = 3 Then colnum_string = "C" End If If num = 4 Then colnum_string = "D" End If If num = 5 Then colnum_string = "E" End If If num = 6 Then colnum_string = "F" End If If num = 7 Then colnum_string = "G" End If If num = 8 Then colnum_string = "H" End If If num = 9 Then colnum_string = "I" End If If num = 10 Then colnum_string = "J" End If If num = 11 Then colnum_string = "K" End If If num = 12 Then colnum_string = "L" End If If num = 13 Then colnum_string = "M" End If If num = 14 Then colnum_string = "N" End If If num = 15 Then colnum_string = "O" End If If num = 16 Then colnum_string = "P" End If If num = 17 Then colnum_string = "Q" End If If num = 18 Then colnum_string = "R" End If If num = 19 Then colnum_string = "S" End If If num = 20 Then colnum_string = "T" End If If num = 21 Then colnum_string = "U" End If If num = 22 Then colnum_string = "V" End If If num = 23 Then colnum_string = "W" End If If num = 24 Then colnum_string = "X" End If If num = 25 Then colnum_string = "Y" End If If num = 26 Then colnum_string = "Z" End IfEnd Function Is there a better, more simple way to return the string value for a column from it's number reference?
View Replies!
View Related
Search Column And Return Values
ok I have multiple columns where Col-A can have any number between 1 and 5. Col-B has a 3 digit number in it. i want to search all of column A and determine if there is a 1 in it, if so i want it to print the value of col b into a specified area. here is an example of my form. COLA | COLB | -------------- 1 | 324 | -------------- 2 | 325 | -------------- 1 | 327 | --------------- this is basics, i will be using this to search col-a, determine what number is in the column, then print the number found in col b, on another page in a specific place, on the other page i have squares labeled 1-50. in a grid pattern, 10 squares per row. im trying to get all the entries in col-a, that are a 1, to put whatever value is in col-b in the first box of the grid, whatever is found in col-a with a 2, the value found n col-b of that row, into the grid box labeled 2 etc......
View Replies!
View Related
Search For Text Return Value In Next Column
I'm trying to create a spreadsheet using several others in several different formats. What i need to do is search for a text string eg "EVP" in one column and return the numerical value of the cell in the column next to it - then sum all the values and create a table in the new sheet eg. EVP sheet1!(value)+sheet2!(value)...etc...
View Replies!
View Related
Partial String Vlookup
I have two columns Cust. Phone and State. The Cust. Phone column has the full 10 digit number. Some with the preceeding 1 and some without. The vlookup will sit in the State column. I am trying to perform a lookup using the area code. I have tried the following but always recieve #N/A. =IF(LEFT(CELL("contents",A2))="1",VLOOKUP(MID(A2,2,3),D2:E8,2,FALSE),VLOOKUP(LEFT(CELL("contents",A2),3),D2:E8,2,FALSE)) It makes sense if I walk through it but fails to work. I am trying to use the HTML maker to upload an example but I'm running into problems. I'll update ASAP.
View Replies!
View Related
Partial String Lookup
I need to do a lookup in a column for part of a string and return the adjacent value in a different column. Works fine using VLOOKUP as seen in the following example except that the return value is being truncated. Lookup range in column A: A001,A002 A003,A004 A005,A006 I need to find A001 and return the value in column I of the same row. I am currently using {VLOOKUP("A001", LEFT(A1:I500, 4), 9, 0)} This does work entered as an array formula but it is only returning the left 4 characters of the column I value. I need to lookup the left 4 characters in the lookup range but return all the characters in the return range.
View Replies!
View Related
Conditional Copy Routine: Macro To Search A Column For A Specific Text String
I am trying to write a macro to search a column for a specific text string which when found, will copy the whole row the string is in. Once this row has been copied, I then want the macro to activate a new sheet and search for the next available empty row to paste the data. Once this has been done, go back to the original sheet and find the next cell in the original column with the specified text string and repeat until the range has been satisfied. Below is the script I have that sort of works.
View Replies!
View Related
VBA Keyword Search To Return Row Numbers
i'm writing a few VB subs to help manage a database of mishaps, solutions and lessons learned, there are 11 columns and each databade entry has a separate row, there are approximately 1,000 rows. Columns are: ID- a unique iderntifier for each entry- integer Date- date the entry was made- date Project Number- a reference to an internal project file- string Operator- company we were working for- string Installation- where the work took place- string Category- type of problem encountered- string Application- field of problem encountered- string Issue- string Background- string Problems- string Lessons Learned- string...........
View Replies!
View Related
Search A Column & Return Many Results
I have a workbook with two worksheets. On Sheet1 I have two columns 'Date' & 'Serial Number' I want to search the 'Date' column for entries that contain todays date & then display the corresponding 'Serial Numbers' in the first column of Sheet2.
View Replies!
View Related
Macro For Partial String In Text
I want to write an macro where it searches for text in a column, but the text may be a partial string. The text is in column B, with account numbers in column A, like this: Column A Column B 1100 Jay 1101 Jack 1102 Jackson 1103 Jacksony 1104 Jefferson For example, I want to search on Jack in column B. In the above example, I want it to find accounts 1101, 1102, and 1103, and to copy that information to another spreadsheet, say, sheet2. If possible, I'd like it to copy the first account number and name, 1101 Jack, and if that's not what the user wants, they click next and they see 102 Jackson and so forth.
View Replies!
View Related
How To Use VBA To Search Strings And Return Values Or Cells Nearby
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 Replies!
View Related
Vlookup: Search For Value In SECOND Column Of Table Array And Return Value From FIRST
I'm looking for a formula to search for a value in the SECOND (instead of first) column of a table array and return a value in the same row from FIRST (instead of the same or another) column in the table array. Formula would be searching for the unique production order number in the column B and return production line id from the column A. A1 production line_id B1 poduction_order_number A2 L1 A3 L2 A4 L1 B2 505212 B3 504234 B4 505663 I was trying vlookup(504234;B2:A4;2;0) to make formula go search from right columns to the left but then excel is switching the search table to A2:B4 and gives #N/D! One remark-there is no possibility to switch these 2 columns to simplify. I have to leave them as they are.
View Replies!
View Related
Search For File With Wildcards And Partial Filename
I am trying to set a file name as a string to use it as an attachment. The user inputs a number into a textbox. That number is used to locate the file in location. But the file name is different with each file. So im trying to do something like "C:Desktop*TextBox1*.docx" Whereas the actual file name is "C:DesktopMCJC RFI 1 MISSING ROOM NUMBERS.docx" The location "C:Desktop" will always remain the same as will ".docx". I have everything else working just fine.
View Replies!
View Related
Sort Pivot Table Based On Partial String...
I've got a group of data within a pivot table that I want to be sorted based on only part of the string. For example, Standard Kitchen Standard Master Bath Standard Hall Bath Optional Kitchen Optional Basement Bath First they would be sorted by whether they begin with Standard or Optional. Then within those groups, they would be sorted whether they contain Kitchen anywhere in the string. So that the order would be Standard Kitchens, Standard Anything Else, Optional Kitchens, Optional Anything Else.
View Replies!
View Related
Summarize Data Based On Partial String Matches
I have two questions: a. Check the code below: Dim news1 As String, news2 As String news1 = "new" news2 = "polygon" Dim countie As Integer For buddie = 2 To b1.UsedRange.Rows.Count 'If b1. Cells(buddie, 1).Value Like "new*polygon" Then If b1.Cells(buddie, 1).Value Like news1*news2 Then countie = countie + 1 n1.Cells(buddie, 10).Value = "test" End If Next buddie I have 2 strings, news1 and news2, i need to use the like function to check the occurance of these two strings in all the cells, and just for testing purpose, im printing 'test' it out in another excel sheet. How do i make that work? the commented line shows what exactly i want, only that i dont want to hard code the string values. b. If i have a value in the cell: /new/blahblah/anycra/polygon how do i split it so as to assign two variables to have 'new' and 'polygon'.
View Replies!
View Related
Cell Partial, VBA
I have a cell that contains a string which is always formatted the same. "Dated between Date1 and Date2" I would like to put Date2 into a variable; however, I don't know how to get just that part of the cell.
View Replies!
View Related
Deleting Partial Content In A Column
I have a sheet with 3 columns, A B and C column B has content such as: term 1 (c: text a) term 2 term 3 term 4 (c: text b) etc. desired output: term 1 term 2 term 3 term 4 etc. Can someone create a macro for me to delete the (c: ...) in Column B but leaves all of the other column data alone.
View Replies!
View Related
VBA Code To Go Through Column Cells & Return Value Based On Cell Value
I have been stuck for about a week on trying to create a Case Select macro. I am a novice in using VBA, so I am not sure what I am doing right OR wrong. What I need the macro to do is go through all of the text in a column and if the text is one of the 50 states, I need it to return one specific value, and if it is anything other than one of the 50 states, it needs to return a different value in a column 5 over from the column with the states in it. I have changed the code MANY times and so far I haven't been able to get anything to work. Here's the gist of what I have been trying: Sub usstates() Dim R As Range, s As String Set R = Sheets(1).Range("J1:J50000") For i = 5 To R.Rows.Count X = UCase(R.Cells(10, 1)) If X = "AL" Or X = "AZ" Or X = "AK" Or X = "AR" Or X = "CA" Then R.Cells(i, 2) = "SC" End If Next i End Sub.................
View Replies!
View Related
VBA: Search Function: Which Shows An Input Box Where You Can Enter A Word To Search For
I'm from Bavaria, Germany. Right now, I am doing an internship for my studies. my problem: I need a search procedure which shows an Input Box where you can enter a word to search for. It should work like the original Excel search (Ctrl-F), but with a simpler design, like with my own Text "Enter your Query" and a Button "Submit Query" / "Quit search". Is there the possibility to Highlight the Search Target? The problem hereby is that this highlighting should not be permanent. That means the user sees the target for which he searched for, the cell highlighted in a different color, etc. But as soon as the user clicks onto another cell, etc., the highlighting vanishes. If there is no fitting match, there should be a MessageBox like "Sorry the Target xyz cannot be found"
View Replies!
View Related
Search For Sub String
I have a column with text that can contain a number of words one of which is 'UK & Europe'. I want to have another cell which will contain the value EMEA if the the cell mentioned above contains the word UK. how to do this as I'm stumped... I get a #value in cells that dont contain the word when using the formula =IF(FIND("UK",H27),"EMEA","ROW")
View Replies!
View Related
Search For All Instances Of A String
I'm using the following code which is working well. It finds an instance of the string "A83". Once it finds it, it moves down x number of cells and populates it with a new string. It then moves down another x number of cells and populates it with another new string, etc. The problem is, it is only looking for "A83" one time and performing the appropriate actions at that point. What I need to do is look for *every* instance of the string "A83" and perform the appropriate actions.
View Replies!
View Related
Search For String Within Cell
I use the following code to search through a database and paste the results in a separate worksheet. Private Sub TextBox8_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) sheets("Search Results").Unprotect Dim cell As Range Dim foundcell As Boolean If KeyCode = 13 Then Application. ScreenUpdating = False Application.enableevents = False If Not TextBox8.text = "" Then For Each cell In sheets("Detail").Range("e2:e" & sheets("Detail").Range("e65536").End(xlUp).Row) If cell.Value = TextBox8.text Then foundcell = True With sheets("Search Results").Range("e65536").End(xlUp).Offset(1) .Value = cell.Value .Offset(, -4) = cell.Offset(, -4).Value .Offset(, -3).Value = cell.Offset(, -3).Value .Offset(, -2).Value = cell.Offset(, -2).Value .Offset(, -1).Value = cell.Offset(, -1).Value .Offset(, 1).Value = cell.Offset(, 1).Value ................................
View Replies!
View Related
Search A String For A List Of Matches
I am looking for a way to check for the presence of a member of a list in a field. Not for an exact match, but for the text to be somewhere present. I know I can use the "search" function to find text within text, but I don't see how to find the presence of any member of a range of values (a list) within the text.
View Replies!
View Related
Search A Cell For A String Of Numbers
I have the below data to look at every day. The Data codes column A contains a part number and the Info Column B, the information about the part. I have in column C the serial numbers that change daily, I copy and paste this column in everyday.What I would like to do is search within the serial numbers for the data codes and return in the Result column D the info relating to the data code. Data codesInfoSerial numberResult118Type A118F2041Type B118F BNG59617Type C2041F2151Type D2066F=9617570Type E2151F800Type FEXPEDITE COPY:570F=MEDIACOM PDF687Type GEXPEDITE:118F Currently I spend hours removing all of the text and symbols (there are normally 2500 to 3000 serial numbers) and then do a simple vlookup. Is there a formula that could search the serial number to find the data code? Or some code I could use in a macro to do the job for me? The serial numbers change daily but the data codes and info in the first 2 columns remain contstant - there are nearly 250 data codes.
View Replies!
View Related
Search Comma Delimited String
I have a spreadsheet with a number of columns containing comma delimited strings (years) which I need to check cell by cell and return false if anything apart from the years 2001 to 2008 is found. For instance, a cell may contain the years (2001, 2005, 2006, 2007, 2008) or (2006, 2007) or (2001, 2004, 2008) or (2004) or any combination of those 8 years. I thought of using the Split function on each cell and then looping through the resultant array to do a comparison against each of the 8 years but with a large number of delimited strings to check it could be a bit time consuming. Any idea how I could accomplish this more quickly and efficiently either with a formula or VBA?
View Replies!
View Related
How To Search For A String That Includes The Symbol
I'm writing some VB in an Excel macro to search and replace a string of text. The problem is that the string contains the " symbol, so when Access gets to that symbol it thinks that it's reached the end of the string. e.g. I want to replace "0519","863027" (including the quotes) with "0001","863027" This is what the code looks like, but it obviously doesn't work :
View Replies!
View Related
Formula / Macro To Search For A Particular String
how to use a formula or macro or whatever that is applicable to solve my current issue. For example: I've the following text below. The prefix of "atest", "bgo", "crun" and "dfly_c" are fixed pattern, however, the text after prefix will change accordingly. atest_myhomeisfar bgo_runfast crun_b:veryfast dfly_c:bluesky Now my request is, I would like to have the final output as per below. Notices that there are some additional numbers behind the text (with semicolumn). Final output: atest_myhomeisfar:1998 brun_veryfast:2009 cfly_bluesky:1790 dfly_c:bluesky:1800 My question is, is it possible to create something to handle this issue? I mean something that will seach thru a particular fixed pattern as mentioned above and add the number behind it? I would like to do it one at the time (one prefix at a time). Not mass searching and adding. For example: Search all "atest*" pattern and add an additional number behind.
View Replies!
View Related
Search For Word In A String Of Data
I have put a formula in excel to count how many times the word 'administration' appears in a column: =COUNTIF(K2:K99,"Administration") Unfortunately, the output that I am searching has mulitple words in it, separated with a colon and no space. My formula skips the count if the word Administration is not completely on it's own e.g. Administration counts 1 Administration;Cardiology does not count
View Replies!
View Related
Vlookup To Search For Part Of A String?
If I have a store number (say 1234) and I am trying to search for something which contains 1234 (i.e. the whole string of ABCD1234, ABCD1235, ABCD1236 etc) is it possible to do this in one VLOOKUP formula? One way of doing it would be to VLOOKUP("ABCD"&"1234",....) but I would rather do it the other way around, to prevent the formula from not working.
View Replies!
View Related
|
TRACKER
