Search For Text Within A String, Lookup And Then Return Item
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
ADVERTISEMENT
Dec 1, 2011
Searching a text string on Sheet 1 to see if it contains any item in a list (on Sheet 2 Column 1) and if so return the value of Sheet 2 Column 2 next to the list item found?
It's a budget problem: Sheet 1 has my downloaded Visa statements. I want to categorise all items.
Eg Any item containing the string 'safeway' is categorised as 'General expenses'. So the list on Sheet 2 has an item called 'safeway' and in the next column 'General expenses'.
And for the item on Sheet 1 'BPAYN BUPA AUSTRALIA BPAY MBF monthly' I have an item in Sheet 2 that is simply 'BUPA' with category 'Medical expenses'.
View 6 Replies
View Related
Jan 29, 2010
I need to look within column A (which contains text strings), match label in column c (which contains text for label), and return result within column B (titled category label).
Essentially, I want to label my data based upon a specific element found within column A with a label list in column C.
I have attached an excel file that shows the current problem. Nested if(isnumber(search( statements can perform this, but I yield too many arguments error after three labels?
Isn't there a way to do this with Vlookup or Hlookup?
View 9 Replies
View Related
Mar 15, 2007
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 9 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
Feb 12, 2014
I have a worksheet with following values:
A
B
C
1
Shorts
75
[Code]...
Also I have a Userform with 2 ComboBoxes named "ComboBox1" and "ComboBox2". Values in ComboBox1 is "Pull my pants", "Eat my shorts", "Socks for everyone".
What I would like to do is to search though column A and look for any of these textstrings in my selection in ComboBox1 and return the value from column B in column C.
Example: "Pull my pants" is selected in ComboBox1 then the value "pants" should be found in column A and value in column B (25) should be entered in cell C2.
I am fairly new to VBA and have spend hours searching Google and found some formulas like InStr and VLookup. My problem is that I am not sure if these statements will do the job and how to combine them.
View 1 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
Jan 28, 2014
I'm trying to come up with a VBA function that would return cell values based on another cell value.
I can do this by comparing a range (column of cells) with the cell value to match and then return the value of the cell next to it.
For instance,
The function should search a column for partial text, as follows:
TS ID
PDT ID
TS 1.1
PDT 1
TS 1.2
PDT 2
TS 1.3
PDT 3
TS 2.1
PDT 4
TS 2.2
PDT 5
TS 3.1
PDT 6
TS 3.2
PDT 7
In the above table, the function should for partial text and return string as follows:
TS covered
PDTs
TS 1.1, TS 1.2, TS 1.3
PDT 1, PDT 2, PDT 3
TS 2.1, TS 2.2
PDT 4, PDT 5
TS 3.1, TS 3.2
PDT 6, PDT 7
So basically I am searching for partial text TS 1. and so on
View 4 Replies
View Related
Feb 13, 2008
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 3 Replies
View Related
May 27, 2014
In the attached excel, I will be putting a string (Which will be a result of another formula) and there will be a table in column A & B(Length of the table will vary).
What I need is a formula to search entire column B and get the file names which is having that string and also the corresponding path
Table E6 to F9 contains the desired result (The result wont be more than 3 at any point of time
Search.xlsx
View 3 Replies
View Related
Mar 22, 2007
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 9 Replies
View Related
Jan 31, 2008
Find a short text string in a column of longer text strings and when that short text string is found return the longer text string that matches.
View 3 Replies
View Related
Jun 29, 2014
I need to create a macro to do the following:
Search the activecell for a text string (a), and then either paste in text string (b) at the end of the cell if (a) is found, or text string (c) if (a) is not found.
For example, if the activecell has "AA/" in it, I want the cell to become "AA/01" (pasting in "01" at the end), and if the cell has just "AA" in it, I want it to still become "AA/01" (pasting "/01" at the end). The macro will be linked to a commandbutton.
View 7 Replies
View Related
May 27, 2013
I am trying to search words that are in column A (5 letter combinations of sequences) within the text in column B (amino acid sequence).
So I am stumbling upon 2 questions:
- what is the function that would do this search.
- how to acchieve to reverse the text in B1 cell (eg. abcde --> edcba , but with 600 letters),
sample file: test.xlsx
View 4 Replies
View Related
Jun 5, 2013
I have a spread sheet with a large amount of tabs and I want to search against a part number that would only ever appear once on each tab and return the number in the cell to the right of it using vlookup against each tab. is this possible?
View 2 Replies
View Related
Jun 13, 2014
I need to find a formula which will search a list of cells looking for a particular text string. If it doesn't find this text string it then needs to search for the next one. For instance if I had a list of product codes: ABCD1234, BCD1234, ABCD2345, CDE23456, BCD2345 I want the formula to look up and see if the cell contains the text ABCD and if so return ABCD as the value, if not I want it to go on and see if the cell contains BCD and return the value BCD, if not then go on and search the value CDE etc. It seems like an easy job to do manually but I have a list of over 3000 codes to do this.
View 4 Replies
View Related
Jun 11, 2008
I am using VB and need to search each cell in the row for a string "U30". If it appears, then I need to grab that plus the next three digits, "U30XXX". Otherwise, I need to grab the last six in that row (=RIGHT(M2,6)). I am not sure how to structure the If statement in VB. I searched the forum and help files, but was not sure what to do from here. Currently, I have...
Range("I2").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[4],6)"
Range("I2").Select
Selection.AutoFill Destination:=Range("I2", Cells(Rows.Count, "I")), Type:=xlFillDefault
View 7 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
Dec 3, 2009
I have a text such as:
fdfs&s_kwcid
sfsd&s_kwcid=dfsads
&dfsdaf&dsafdsf&s_kwcid=
fdsf&dasfsdf&s_kwcid=dsfasfsdf
I want to do is search for "&s_kwcid" or anything containing "&s_kwcid" and replace it with blank. So above would then read:
fdfs
sfsd
&dfsdaf&dsafdsf
fdsf&dasfsdf
I tried =IF(SUM(COUNTIF(E2,{"&s_kwcid*"}))=1,E2,"") but it didn't work. I tried auto filtering, and using contains &s_kwcid* but it didn't filter out results, but find &s_kwcid did find results for text anywhere in string, so I know the problem is there.
View 5 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
Feb 18, 2008
i have been trying to find in C1 the 1st product in a list of products that is in text strings of various lengths and the product will be in different places each time eg; list in A1:A4 is babydoll camisole corset basque, the text string in B1 is Satin strech babydoll with low neck
in B2 the text string is Beautiful lace basque with corset style back.
in C1 i'm looking to find babydoll from B1 in the list A1:A4
in C2 i'm looking to find basque from B2 in the list A1:A4
in D1 i'm looking to find any 2nd match from B1 in the list A1:A4, result = ""
in D2 i'm looking to find any 2nd match from B2 in the list A1:A4, result = corset
in E1 & E2 i would like to find any 3rd matches
i have been reading up on this and trying formulas but i cant figure out how to find from a list and the text string data is not in a standard order.
View 9 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
Jan 6, 2014
Let us say I have the following text in Cell A1. "There is a fault in the cal cycle.Need to update the records." And in cell A2 I have the following text. "Called for backup assistance. There is an issue with numbers." I cells B1 & B2 I need a particular formula which searches for the substring "cal" exactly and returns true if present. In cell A1, we have "cal" in the text. So it should return true for me in cell B1.
However in cell B2 I need false to be returned even though I have "Called" inside the text. I need true to be shown only for those cells where we have the exact text "cal" and no text characters in front or back of it.
View 3 Replies
View Related
Feb 4, 2010
Can you please assist in helping with a formula that will return the value of the characters (no matter the length) between the "."?
123456.B.78.C.321
12345.B.78.C.321
View 10 Replies
View Related
Jun 13, 2006
I have a list of product numbers in col. A. In col. O I have a list of file
names that contain the product numbers as well as additional characters. I
need a formula that will search col O for the first instance of the text in
cell A2 and return that value.
The next formula will return all instances that contain the text found in A2.
View 12 Replies
View Related
Apr 7, 2009
Imagine the columns below are labelled A - D. Is there a way to search for the value 'SPLT' and return the value in column D?
Great Scot Dried Peas 3610.6366
GT SCT GRN SPLT PEAS480.151
GT SCT MRWFT PEAS1230.2158
GT SCT SPLT PEAS 1900.3157
Haven 370.10
View 9 Replies
View Related
Oct 17, 2006
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 3 Replies
View Related
Aug 7, 2013
I am trying to automate the account classification of credit card (cc) expenses. The problem is charges to USAirways will always have random numbers after the vendor name on the cc download. I set USAIRWAYS as a Travel Expense and a simple Vlookup will not work.
For example, let's say a data dump in column C of the CC:
cell C2 is USAIRWAYS 037232A49429
CeLL C3:
USAIRWAYS 037282hf39647
Cell C4:
Facebook A8476H4
In column E I have my simple vendor name. Column F as the Vendor Account. For example E1 is USAIRWAYS. F1 is Travel Expense. E2 is FaceBook. F2 is Marketing Expense
In cell D2 & D3 I want to return Travel Expense anytime the formula sees USAirways in cell C2 returning the answer in column F after matching w/ column E. In cell D4 I want to return Marketing Expense.
View 3 Replies
View Related
Jan 9, 2009
not sure if an "IF" staement is appropriate?
I have 3 columns containing data:
column A has text in it,
example: A2="red", A3="green", A4="blue"
column B has 3 letter month in it and they could have repeats
example: B2="Jan", B3="Feb", B4="Jan"
C2="Jan"
in D2 list anything column A that matches C2
example: D2="red, blue"
View 11 Replies
View Related
Jan 8, 2014
Formula that could find the result and return the greatest text value?
I have over a 1000 records broken down by Leaders of which each is roughly 50 records. in those records, answers are either : 4 hours, 8 hours, 24 hours or 48 hours. I need to have a formula return of the 4 choices, which had the greatest value.
View 5 Replies
View Related
