Formula To Search A Cell For Specific Data To Extract
Mar 13, 2009
I was wondering if there is a formula that will search a cell for a word or other specific criteria then if it finds it, puts the requested data / word in the formula cell
Example:
If I have a list of vehicle descriptions all in different formats:
CAR1 1.4 SRI 3 door Hatch
CAR2 5 door saloon GSI 2.0 V8
2.2 CDX 5 door CAR3 Estate
CAR3 Estate 5 Door CDX
Say I want to know which ones are CDX varient I need the formula to look in the cell and return "CDX" or "YES"
View 4 Replies
ADVERTISEMENT
May 20, 2014
I need a formula to extract data after "<li>Color: " & before"</li>"
A
B
1
ORIGINAL
RESULTS
2
blah blah blah<li>Color: White</li>some informations
White
3
blah blah blah<li>Color: Red</li>some informations
Red
4
blah blah blah<li>Color: Black</li>some informations
Black
5
blah blah blah<li>Color: Red</li>some informations
Red
I have already use this formula but I get the "#VALUE!" error even if the cell contains the "<li>Color: </li>" data.
[Code] ....
Sometimes, some cells does not contain the "<li>Color: </li>" data, I don't want the "#VALUE!" error, I just want a blank cell there.
This is an example with the real values I want to extract Example.xlsx
This is the formula that works
[Code] ......
View 8 Replies
View Related
Dec 2, 2011
I have data in several cells, and I want to extract only the information in the cell up to a /. For example "joesmith/shipping" I would like to have only joesmith in the cell next to it.
View 2 Replies
View Related
Sep 16, 2009
I have one workbook (workbook A) with data in it (the data is never in the same place i.e. same cells) and one I want to send data to (workbook B)
In workbook A the data is displayed as follows:.....
View 9 Replies
View Related
Nov 15, 2011
I am having trouble getting a formula to work with exactly what I need. I need a formula to find the total quantity of each product. The file I'm searching from does not list the product name or description in the subtotal column. The host file is similar to below and I just want the product name and total quantity in the new worksheet. I will have a list of all products we carry in the new worksheet and I want to pull the total quantity ordered for each item, and if none were ordered return a quantity of 0. The program the host file is exported from also lists a header at the top of each page, so for example there may be a header in between the last occurrence of Product D and the Subtotal.
Product A Description Sub description Date Ordered Quantity Ordered
Product A Description Sub description Date Ordered Quantity Ordered
Product A Description Sub description Date Ordered Quantity Ordered
Product A Description Sub description Date Ordered Quantity Ordered
Item Subtotal Total Quantity
Product B Description Sub description Date Ordered Quantity Ordered
Item Subtotal Total Quantity
Product D Description Sub description Date Ordered Quantity Ordered
Product D Description Sub description Date Ordered Quantity Ordered
Product D Description Sub description Date Ordered Quantity Ordered
Item Subtotal Total Quantity
View 2 Replies
View Related
Dec 21, 2013
I paste new data into a sheet of a monthly report I prepare. For this sheet, the # of data rows change (and is unpredictable) every month. I need the value inside a specific cell that dynamcially moves up and down based on the # of rows for that month (because it's below the rows of data).
So I made a formula to identify the exact cell # every month.
Example:
This month the exact cell is F255 in the "Refi" sheet.
So my formula in the "Summary" sheet cell A1 first finds the cell row # only (255) and since it's always column F, in B1 I have
VB:
="F" & (A1)
This outputs "F255" in B1, successfully identifying the target cell.
Now how do I write a formula in C1 to grab the value from whatever cell is named in B1. (For this month, the value in cell F255 from the "Refi" sheet)
View 7 Replies
View Related
May 31, 2007
From mySQL database, i am getting a ton of data that is all being inputted into a single excel cell. Within this cell, there are certain data points that i wish to obtain (Bank #, SSN #, Routing #). There are multiple difficulties in obtaining this information.
1. these cells contain the same fields, but different number of total characters (differing in names, addresses, etc.)
2. some of the data points (like SSN) are inputted incorrectly, so even though a SSN is only 9 digits/characters, I may have to output all 11 that the SQL database placed within the cell.
Can I get some help? I'm thinking of a search function/macro within excel, because all of the fields show up correctly.
something like (i know this doesn't work)
= or("cellA1" contains/finds/function SSN,return the 9 characters after "SSN ")
um...yeah. difficult to explain, i'm sorry!
edit: not sure what is wrong the file. I have attached a new one.
View 9 Replies
View Related
Nov 6, 2007
I'd like to have a formula in Column C which would extract the quantity from either Column A or Column E depending on the value in Column D.
A ---- B ---- C ---- D----------------E-----------F
QTY -UOM----------Tariff #----------QTY 1-----UOM1
46----PCS---------3402.12.5000----94.3--------KG
85----PCS---------3402.12.5000----174.25-----KG
4-----PCS----------3701.10.0030----4.8---------M2
5-----PCS----------3701.10.0030----1-----------M2
15----PCS----------3701.10.0030----1-----------M2
View 9 Replies
View Related
Sep 14, 2008
I'm trying to search through multiple worksheets (that are closed) to see if a value in cell B12 (of every worksheet) matches a value in a seperate worksheet (which is in a seperate workbook)
View 14 Replies
View Related
Jan 4, 2010
I want to extract specific expense for my year 2009 work book. such as just rent with relevant data from all other expeses.
for example in my work book, I want to extract rental expense with relevant GST PST and Total for all three months. i attached a excel work book for broader idea.
View 5 Replies
View Related
Jun 14, 2013
I'm trying to find a way to use a macro to extract data from a specific column from Sheet1 based on the columns header/title and copied into Sheet2 into the respective column with matching header/title.
For instance, in Sheet1:
Chicken
Cow
Donkey
Pig
[Code]....
So the above table would be the result i'm aiming for.
NOTE, its not different workbooks. I'm looking for sheet to sheet macro.
I've attached a file as well if someone wants to have a go at it. There are no codes in it.
View 3 Replies
View Related
Feb 22, 2007
In column A I have various part numbers with alph-numeric characters. In column B I have a similar list. In column C I have the quantities for the part numbers in column B.
What I need is for say a macro or forumla to look at each part in column A and match it with the part in column B and in column D insert the appropriate quantity from column C.
I'm not sure if this falls under say a filter, extraction or search type of function.
View 14 Replies
View Related
Jul 15, 2014
I'm trying to extract some data from an online page, but I require a specific cell at an exact time, each day.
For example, I would like to acquire the main data from the following page: [URL]
into excel, but additionally, I require the ESU14 (Sep '14) Open cell at exactly 0700 BST. I am currently using the 'Data' - 'From Web' feature to scrape the necessary fields into Excel however I haven't found a way to acquire one of those cells, at exactly a certain time, every day, even when Excel et al aren't open.
View 1 Replies
View Related
May 19, 2012
I want to extract data in table with this order C7:AA7
Sheet2 *ABCDEFGHIJKLMNOPQRSTUVWXYZAA1LetterNo
*************************2A5*************************3B15*************************4C3*************************5D2
*************************6***************************7**ABCDABCDABCABABBBBBBBBBBB
View 9 Replies
View Related
May 30, 2012
i want to extract the data from the table
Sheet2 ABCDE1Data 2ABSCar1Car3Car6Car13ADFCar2Car5Car3Car24ANDCar1Car1Car1Car35ADXCar6Car6Car5Car1
6 7 8 9Results10 Car1Data11 ABS12 ABS13 AND14 AND15 AND16 ADX17 18 Data19
Car2ADF20 ADF21 22 Data23 Car3ABS24 ADF25 AND26 27 Data28 Car5ADF29 ADX
View 6 Replies
View Related
May 7, 2012
I am using some code to loop through a folder and extract some data from a specific sheet.
Now say a user moves the file to another folder etc... The macro will pull up an error, now is there a way i can have it dynamic linked to the spreadsheet so no matter what folder it gets moved to,
My folder path will adjust to that or need to take another route?
View 2 Replies
View Related
Aug 7, 2006
I have been trying to work this out by looking at other posts (mostly concerning Binary Access) but can't figure it! The source text files I am using can vary in length from 4,000 characters to well over 100,000 characters. However the data I am looking for always starts 40 characters from the end of the file and is 10 characters long! I need my macro to pick out this data and store it as a string (so it can be added to an array and exported to a worksheet later)
View 2 Replies
View Related
Jun 30, 2014
I need a macro, or a formula that can identify if the words in the Words Column (Column A) is contained in Title Column (Column B). If it is, It displays as "Yes". If not, display as "No".
Case is not sensitive.
Words
Title
Displayed?
Christmas
Coworker has chronic hiccups
Yes
Excel
I Love Excel
Yes
Cartman
I Like Turtles
No
Ninja
Oh Christmas Tree
Yes
Tiger
Case of the Mondays
No
Chronic
Cute Monkeys
No
View 4 Replies
View Related
Oct 3, 2008
I have a few hundred data/text files to analyze, some a few thousand lines deep. I would like to grab 5, just 5 numbers describing voltage levels. I know the preceding text for each of these in the cell and the following text as well. I just need to delete everything else and keep these 5 values in a column using a script or a macro, and do so for all these files in a specified folder.
This would be 3 weeks of work and the analysis needs to be done ASAP for my research.
Thousands of lines of ancillary data
Thousands of lines of ancillary data
Thousands of lines of ancillary data
Thousands of lines of ancillary data
"11/09/07 14:42:20 J13-50 and J6-9, PCU Resistance (XXXXXXX. Ohms) is within 1.000 to 3741."
Thousands of lines of ancillary data...........................
View 9 Replies
View Related
Feb 27, 2014
I've had a look at some examples of extracting unique values but it seems quite complex to me!
I have a list of customer names in column A, column B contains a material code (number format), column C contains the material information sheet (text & number format), column D contains the date that the material information sheet was sent (all column headers are in row 1).
Columns E, F & G follow the same format as above but for a different material code - this is repeated further in Columns H, I & J.
All of the material codes are different, but some material information sheets can be the same for some materials.
In column K I need to identify the unique material information sheet values for each customer, as some are duplicated.
Is this possible? There may be some blank values in the row i.e. a customer does not receive a certain material.
Perhaps just an easier way of organising the spreadsheet!
View 9 Replies
View Related
Dec 29, 2008
I want to use vlookup up function to search for a specific data set. when it finds it i want it to look 2 columns over and add the value then continue to search for the specific data set again in the range provided in the vlookup, when it finds another matching the criteria look 2 columns over and add that cell value to the previous count.
keep a running total until it has looked through the whole sheet. here is an example of the vlookup i am using, it is not suming because thats where im stuck. also a quick data set to picture what i am talking about. In the data set in plain terms i want to look for the line name and add the qty on that line into a chart.
=IF(ISNA(VLOOKUP(A8, 'Web Queries'!G$3:H$40, 2, FALSE)) = TRUE, 0, VLOOKUP(A8, 'Web Queries'!G$3:H$40, 2, FALSE))
LineOrder_NumQTY
7L58545487320
7L5856060351
Z_56784817515
Z_56784824115
Z_57454577222
Z_57454562424
Z_57454547524......................
View 5 Replies
View Related
Mar 22, 2012
adapting the bolded code to search for a specific row where the date displayed in "DAT2" (a date in Column A) occurs in the same row as specific data (e.g CEF1) in column B (note that there will only ever be one combination like this on the worksheet but it could occur in any Row) and then display just that result in "Experimental".
Private Sub CommandButton1_Click()
Unload Me
Dim CellFound As Range
Dim Rng As Range
Dim Wks As Worksheet
If TextBox1 = "" Then
MsgBox "You must enter a Date to Find."
Exit Sub
End If
[code]......
View 1 Replies
View Related
Nov 15, 2013
I have a workbook with several sheets of data - it's basically names with a series of assessment data. Each name and selection of data is in its own row.
I want to build an additional sheet with a search function/drop down box that wil allow any name to be searched for/selected and return that specific name/all/specific cells in that row. I believe this could be done either using VLOOKUP or a Macro but I am only a beginner with either of these things.
View 7 Replies
View Related
Feb 1, 2007
I have a spreadsheet of sales figures that I want to interrogate to show data relating to sales of specific manufacturers products to specific customers, I have a colomn (call it A) that has the customer name listed, coloum B that has the Manufacturer name, coloum C that has the type of product it is and coloum D, coloum E, coloum F, coloum G that has the sales figure for the last 4 quarters.
I would like a user input box to pop up on loading, which asks for the Manufacturer I want to search for, and then a report genertaed that lists each customer with their sales figures by type of product for the specified manufacturer for the last 4 quarters (individually not tottaled). If the customer doesnt have any sales figures for that Manufacturer I would like that listed showing '0'
View 2 Replies
View Related
Apr 4, 2013
I have a sheet with a country in it in A1 (validation list).
Depending on the country in A1 a country specific IF command has to be executed on a column1 with first cel = A3. This IF command also relies on the values in column 2 and 3 on the same row. (B3 and C3 are in the IF cmd)
I taught to do it like this : =IF($A$1="Spain";$A$5;0) with A5 being the country specific IF command which should be executed when spain is selected. however when I do this, this only works for the first cell ( I cannot drag this formule down, since it will always give the value calculated in column 2 and 3 on row1.
I'm not sure if the best way to 'select' the country specific IF cmd is with another IF command..
View 5 Replies
View Related
Mar 21, 2013
I'm building an inventory report based upon a daily report downloaded from a website. The downloaded report changes from day-to-day. All of the data I need remains there, but the location of the columns changes, with the exception of "BOL". I need to sort all of this data in a macro. I have most of my macro figured out, but I'm having trouble with one "sort". I need the data to be sorted FIRST by whether or not the 4th column contains "Clear" or "Dyed". I have the next two sorts already coded, based on SPOT vs Contract and then numerically by column A. But I cannot find macro coding or an excel formula (either would work) that reliably searches the 2nd column for the word "Clear". I have tried different combinations of IFERROR, FIND, SEARCH, IF, ISNUM, etc. in hopes of writing a new adjacent column containing the values "Clear" and "Dyed". I have found many combinations that work if I search only the correct cell. But when I extend the range to search through ~26 cells in one row, they all fail. Below is a (small) example of what I start with and what I want to end with. I've also included my current relevant Sorting code in my macro.
FinPro
Sup
Veh
1Dn
2Cn
2Dn
Bio
Injec
[code]....
View 5 Replies
View Related
Jan 15, 2010
I have a code which can search a column of data and register if any of the cells are equal to eg. "LOMM" or another given shortname.
But sometimes the cell that i want to compare to "LOMM" contains multiple shortnames. (eg. "LOMM, GIV, ANDHE").
In such a case I cannot use the if cells(i, y) = "LOMM" then...
I need an "if"-statement which can identify LOMM in a cell containing multiple shortnames.
I have tried using the FIND and SPLIT functions but it does not work.
View 7 Replies
View Related
Jan 1, 2013
I build a VBA that search for 445 files in folders on specific path.
The code opens each file, take each time the same 31 rows, filter them and paste in the main data base file and then close the origin file.
So, for 445 files it takes about 6:30 minutes.
View 4 Replies
View Related
Jan 2, 2009
I cannot find Excel files that contain specific strings. For example, if I create a new file, and in the first cell type in "123456". Then save the file to the folder c:Temp Then in Windows Explorer, right-click on C:Temp and select "Search . . ." Then under "A word or phrase in the file", type in: 123456
It will NOT find the file! It will find a txt file or a doc file, but not xls files.
I thought maybe it fails because that is a number. But I have hundreds of Excel files with Integer numbers stored in "General" format cells and I desperately need to be able to search them to see which files contain certain numbers. It all started with some files that were sent to me - they work PERFECTLY for searching cells for numbers. So strange - yet if I open the file then save it . . . no changes or edits at all, just Open and Save. . . then the search fails for all numbers in that file. This is so perplexing that I have placed 2 zip Sample files on my website for you to Download: http://www.infocellar.com/Samples.zip
Unzip the two files to any folder. One is named "Sample-Works.xls" - Then do a Windows Search for any number in the file, here is one to try that is in the two files: 174026 And it will list "Sample-Works.xls" but it will NOT list "Sample-Fails.xls", even though BOTH FILES ARE IDENTICAL! I copied the same file twice and then named them. The only difference is that I opened the file Sample-Fails.xls and then immediately Saved it, which as I stated earlier, makes it fail all Searches from that point forward.
View 5 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
