I am trying to extract only partial information from a cell that contains
mutliple entries without seperator. Ie I want to get 3rd reference from
cell containing abcde(I want to extract onto another sheet the "c" and only
the "c")
I'm trying to extract information from a cell that I cut and paste from another source:
09/02/2014-12/11/2014 Lecture Tuesday, Thursday 08:00AM - 09:15AM, Nichols Engineering Hall, Room 441
Each cell is different in that it may be a different day or time:
09/02/2014-12/11/2014 Lecture Tuesday, Thursday 08:00AM - 09:15AM, Nichols Engineering Hall, Room 434
I want to make a formula that will extract the pertinent information (day of the week and time), and store it to another 2 cells, making a list as it "extracts":
Day
Time
Tuesday
8:00 - 9:15
Thursday
8:00 - 9:15
I am trying to write a formula which will extract information from a given set of cells. The information format is
XXXX-YYYYY-ZZZZ
What I need to do is pull out the information which precedes the second. so in the above the answer would be XXXX-YYYY. I was thinking of using a mid or left, right formula till I discovered that the # of characters for X, Y and Z can be any length ie they could be
X-YYYY-ZZZZ
XXXX-Y-
XX-YYYY-ZZZZZ
So the formula needs to find the second dash (-) and extract information prior to it.
I receive a workbook on a monthly basis and it arrives in csv format. I need to format it and split it into several pages prior to sending to managers. I'm comfortable with the vba for formatting and splitting it into several pages but I have a problem with some cells that always have data in them in the same (incorrect) fashion.
how to go about fixing these cells and then I can get on with writing that into the macro.
Currently looks like:
A || B || C
No requested || No Supplied || Percentage
4 || 4100%
12 || 875%
6 || 350%
Should look like:
A || B || C
No requested || No Supplied || Percentage
4 || 4 || 100%
12 || 8 || 75%
6 || 3 || 50%
I was thinking of some kind of Left() formula to extract the leftmost character and place it into it's new cell BUT realise that sometimes I might need to extract 2 (or perhaps even more) characters.
I'm trying to extract information from one specific cell, and place it into another. This other thread kind of does what I'm looking for. I wish to extract from the following; "20/04/2009 5:42:51 AM". Placing the date in one cell, and the time in another. All spacing within the quotation marks is respective of the cell. Obviously dates and times would vary.
View 4 Replies View RelatedI have a macro that lists all documents in a selected folder and am looking to add the author/owner, created on date, last saved date as shown in the document properties.
The code I'm using is: ....
I'm looking to find a way to extract an email address an a reference number from an email receipt that i have extracted to excel. The problem is when I paste the information its pastes onto multiple rows and not a single row.
I'm using this formula to extract the email address and can do something else to get the ref number out however the results are on multiple lines which is not good to me.
[Code] ......
I have attached a document to show you how the data extractS on (EXAMPLE TAB) The information I want to extract is the stuff in Red. I then want the results to look like the Results Tab in the example.
Example.csv
I have some data that I have created a report from. However it doesn't show a "true" picture for the stat we want. I have a pivot table set up with data showing the hours worked for an individual. What I am wanting to do would be to cross reference the data but not sure how to compare data. I have provided some make shift data to see if it is even feasible. The end goal is to get a true picture of P.P.H The data we are able to get is in a horrible format. I would eventually like to automate this in VBA but will work on the later. I thought about doing a vlookup but the is no real matching data for all employees. Some of the names are wrong in one system and there is no matching emp ID.
View 3 Replies View RelatedI am not sure if this is possible but I am trying to extract information from a spreadsheet, the problem is that i need only the postcode but this is in a line with the rest of the address as follows:
123 street, town, AB1 2CD
Is this possible as i need to do this for 1000's so doing it individually will take forever!
I am doing an export of info from one system and running an excel spreadsheet to manipulate the data. The problem is..That the exported data sometimes will export info into combined cells. Look at attachment
This is what I need...
I need the ability to search all the info on the POC tab and pull it into the info tab, by searching on a system and returning the POC info..
I have tried the vslookup formula and it will return the first row not the second row which is where the info is that I need (shown Highlighted)
=VLOOKUP(A4,POC!A2:b50,2,FALSE)
I use a spreadsheet (FY-2012) to track approx 700 work requests. I enter the work request number (name of the spreadsheet) in column A, which is hyperlinked to the actual work request spreadsheet. I would like to automatically fill in this cell with the information entered in cell J42 of the work request entered in column A. Cell A5 of the tracker "FY-2012" indicates Work Request "BB-12003". Cell D5 of the tracker "FY-2012" indicates the completion date. I would like to extract the completion date entered in cell "J42" of Work Request "BB-12003" and have it automatically fill in Cell D5 of the tracker "FY-2012".
View 1 Replies View Relatedtools to extract store locator information from a company's website into excel?
For example: [url]
I am under an impression to loop the extracting process with all known US zipcodes and later remove duplicates. If looping is the way then how to pass on the zipcode to the store locator?
I have a string of information in an Excel download from PayPal that I need to upload into QB; but, the information needs to be in a different format. The string from the download is:
Property Address - 1116 South PaxonBorrower " Testa and Son Contractors,LLCLoan # - 1402088Acct Executive " Brandon Tully
But, I need it to be:
1402040;TPAPR;141 North Clinton Street, East Orange, NJ 07017;Leslie P Carter
Is there a formula that I can use to extract specific information from the original string into columns so I can then concatenate the columns into the information I need to upload? Or is there a better way to do this? The Property Address and Borrower Name are not static sizes so I can't use a formula that counts spaces.
My problem is I cannot copy from a Form Control drop down box, and I need to do this for ~10 drop down boxes in about 200 xls files. The files follow a template so the drop down button Inputs are uniform. Here is some more detail:
I’m building an Access Database so that our data (currently in many, many Excel Spreadsheets) can be efficiently queried. The old system was that when asset data was collected, the data would be entered into an Excel spreadsheet, using a Template. So for example:
Project A1234
Name
Main Street Pipe
High Street Pipe
Ref No.
12345
12346
Installation date
22/08/13
19/07/12
X Coordinate
55667
99212
Y Coordinate
77889
12364
We have hundreds of these files, but I managed to get all the files in one folder and Paste and Transpose the data from the relevant Cell Ranges in each file (using a VB sub) into ONE summary sheet. Success; or so I thought.
Unfortunately for one generation of the template (people changed them slightly every year), the template had people enter information into Drop Down Menus. Not a problem when they’re associated with a cell, but these were free floating Form Controls.. so you can’t actually click and copy from them, or call them in VB as you would a specific cell.. afaik.
So my issue is how can I extract the data from those Form Buttons? I’ll try and provide as much info as I can:
Firstly I tried to find some sort of identifier for each Form Button. The dropdowns are called Combo Boxes, (Developer tab > Insert > Combo Box (Form Control) ), and when you select them (by right clicking) and hit the View Code button in Developer Tab > Controls the following shows up in VB.
Sub DropDown44_Change()
End Sub
I’m guessing that the number is related to the button so that’s one useful thing, an identifier of sorts. There’s no actual code there though really from what I can see.. My problem is I have no idea how to extract from DropDown44!
The range of values in the drop down comes from a separate sheet where there’s just a list of the values. There aren’t any cells linked to the drop downs though. If I enter a cell in (Right Click) Format Control > Cell link: then it prints the number (e.g. 4) of the value in the range. So if the drop down options are “Monday”, “Tuesday”, “Wednesday”, “Thursday” and “Thursday” is selected, the linked cell says: “4”.
They didn’t link a cell, then I could call that cell in a VB sub and the job would be done. Unfortunately they didn’t, so I tried Recording a Macro and selecting the drop down form control.. That didn’t really work, except if I right clicked it, I got some more info from the macro VB:
ActiveSheet.Shapes.Range(Array("Drop Down 19")).Select
So is my drop down an Array? My understanding of an Array is that it’s a list of arguments.. all I get there is the name of the button. I am not experienced in VB so this is as far as I’ve got. Perhaps I can get a sub to select the button using the above line, but I need the info from it, i.e. which value is currently selected in the Drop Down.
(Using Excel 2010 but the .xls files are from ~2005)
i have a workbook blank template for my colleagues to fill in, they fill in this blank template and save it under the serial number of the item they are creating. However a lot of errors exist when they input the serial number inside the workbook where the serial number is a duplicate of a former serial number.
Anyhow i was wondering is there any way when they input the serial number into the worksheet that i could get it to save in a separate workbook and then have conditional formatting to check those serial numbers in the workbook to see if they match?
Please check the attached workbook. Sheet "Data" has some bank account data, and whether the dues are paid or pending.
What I need is, I need to extract only the data of Column A and B into a new worksheet, only if G is in the Pending status.
I don't want to use a macro, nor a pivot, nor autofilter. I need to do it through formulas. I have seen this been done before, but can't figure it out. The max number of pending accounts wont be more than 10, so need to just pull the formula down 10 rows. I have already entered the data in the second sheet that would be the result.
This second sheet is sort of a supervisor sheet, so that whenever they want they can open it to see the number of Pending accounts.
I need a formula that will extract #s, some of which appear as a range, from many cells. The #s/range of #s will not be in the same text location for every cell & text to columns converts some of the ranges to date format which is bad. Below is an example of some of the data that may appear. The only constant is that the first name is only the initial w/a period, but there may be a Jr. or Sr. thrown in occasionally.
T. Jones 1
M. Chavez 2-25
B. Bailey Jr. 26-125
the text entered in B2, to split it, so each letter/figure gets his own cell.
like showen in example below.
using excel 2003 + 2007
and if possible no macro's
ABCDEFGHIJKLMNOPQ1datecont.seal
2
ABCD123456-7
ABCD123456-73
I have a directory folder with an active workbook and another workbook id like to copy data from. The Following macro opens up a file in the same directory, copies some data and pastes it in the active workbook. However with this code I have to specify the filename, 'Data.xlsm' in the example code. I would like it to copy data from the only other workbook in the current directory WITHOUT having to specify the name in the code, so just opening it up no matter what filename it has.
In addition I would like to extract the filename from the workbook im copying data from and paste it into the activewoorkbook in sheet 1 Cell A1. I had a look at getopenfilename function but cant seem to make it work for my purpose.
How does one extract a specific sting/words from each cell? Especially if [formatted data] varys in characters (not suitable for regular LEFT, MID, RIGHT functions use).
Sample Question.xlsx
I am accessing a ratings system for horse racing and trying to extract the top-rated runners for each race using a database query. The problem is that every runner and rating is in one cell and separated by spaces. I have tried using text to columns but obviously can't use space as a delimiter as the horse names have spaces in them sometimes. The one cell basically contains the following string...
COCONUT MOON 100 CARIBBEAN CORAL 100 HOWARDS TIPPLE 97
A2 = COCONUT MOON
B2 = 100
A3 = CARIBBEAN CORAL
B3 = 100
A4 = HOWARDS TIPPLE
B4 = 97
So that I have each horse and it's rating alongside it in the adjoining cell, I figure I somehow need to use LEN, RIGHT, LEFT or something but can't think how to do this
I need a formula which would from one cell extract the words in separate cells. I have probelms because the words in the sentence in the starting cell are not separate by spaces but dots. For example: How.do.I.extract.words.from.this.sentence?
View 7 Replies View RelatedI have users filling in data in columns C and D, I need a macro which will select everything entered and lock those cells - well the catch is, if both columns have data. If column C or D are empty I need that row to remain unlocked. No sorting can occur as well.
View 2 Replies View RelatedCould ye have a look at this sheet. I need to extract the cells that are in ColumnB but not in ColumnA.
View 5 Replies View RelatedYes, I am a newbie to VBA. I've tried for hours now to come up with code to make this work, but no go.
Here's the situation:
There are a list of cells all in column A (row ? - ???) that have a string that needs to be searched to locate a number, and insert that particular number into a cell in another sheet, which is in another workbook.
These are actual examples:
*001 RJ 17 17 1,436 316 28 17 0 0 0 0 0 0
*042 RJ 39 39 215 215 0 39 0 0 0 0 0 0
*03A RJ 8 8 282 202 2 8 0 0 0 0 0 0
The numbers needed from these examples would be: 1,436
215
282
End results:Trip 001 = 1,436
Trip 042 = 215
Trip 03A = 282
FYI...The cell data always starts with *, and is followed by three characters we refer to as a Trip, then a space, and then RJ.
The column with the data is always "A", but the starting row may not always be the same. The rows continue down one by one, but there may not always be the same number of rows (each row used will ALWAYS start with an asterisk, though.)
The number that needs to be extracted is always between the 4th and 5th spaces - CHR$(32)'s.
The numbers, once found, then need to go in another sheet into one of the following cells - C28, C29, C30 or C31. Which cell the number goes into depends on which one of the cells, C28-C31, matches the three character Trip text shown after the *. There are currently 16 different sheets that need to be searched to find where the matching Trip number is. The sheet names do not change.
and yes, the people always enter everything shown in the example into one cell.
I have a workbook with two tabs, Summary & January. The january tab has total expenses for the month of january that are subtotaled by last name. i.e. (baker total, decker total..). I would like to be able to use a formula on the summary tab to pull the values of each of the totals for each person from the january tab to the summary tab.
I tried on the summary sheet doing =jan and selecting the first persons name and then going back to the summary tab and dragging that formula down but it pulls all of the other data within the subtotal on the jan tab and I just want each persons total.
one of my suppliers can only provide me with a basic .xls product list as shown in the attached test sheet.xlsx.
Is it possible to create and run a macro from test sheet.xlsm that will extract data from test sheet.xlsx, specifically from below the heading on row 64 down to and above the heading on row 123 and paste into the (new) worksheet in test sheet.xlsm
This sheet is updated daily and all the heading rows are constantly fluctuating as products are sold and added.
I can do this manually easy enough but if there's any way that this can be automated
This could be a bit complicated, but hopefully also useful for others. We have the following sample of 10 cells from a large file with similar data:
N: 1; B: 162; M: 278, 278; T: 24; A: 103, 105; I: 393; Ḥ: 7240, 7390, 7465, 7546, 7617, 7756, 27399, 8380, 8741, 8894, 8985, 9559, 9671, 9741, 10119, 10211; W: 40; D: 766.
N: 2; B: 246, 889, 1136; M: 255, 255; A: 55; I: 286; Ḥ: 22731, 22802, 22857, 22906, 22948; D: 685.
[Code]...
We want to extract the same letters with its numbers and put the same letters with numbers in one column and do this for all letters, but - this is important - while keeping them in their original row. The purpose of this is addition of data and easy rearrangement of the letter plus numbers order, e.g., to: B M N A T I W D Ḥ. As you can see not all the rows have all letters, nor are the seizes the same. There are thousands of such cells. Some parts are partly in bold, we want to maintain that layout after sorting. Such a thing should be easy for computers, but how could this extraction and manipulation be done (I have Excel 2010)?
P.S. There are two spaces after every ; that were eaten up here.
I would like to know how i get excel to put information from say I7 into I94. i want it to be exactly the same information - and its not always a number. It could be a number, or text or a date?
View 2 Replies View RelatedI have rows of cells with the following information..
Row 1, Column 1: Address City/State
Example: 111 Old Creek Road Stanton, VA 25523
Is there any way to separate the Address and the City/State? I would much rather the spreadsheet be in the following format.
Row 1, Column 1: Address
Row 1, Column 2: City/State
or ..
Row 1, Column 1: 111 Old Creek Road
Row 1, Column 2: Stanton, VA 25523
I'm just wondering if there's a fast way to do this, or if I have to go in and do it manually - the reason i'm looking for a faster way to do this is because I have approximately 3000 rows to do it to. :shrugs: