Extracting Number From My Data
Oct 16, 2007
how to get the function in excel 2007 for the following problem please . Col R i am cutting and pasteing from a web page info like this 1/1 or 13/14 or 9/10 . when i paste to my worksheet it comes out as 3-Mar or 10-Sep or 12-Dec , but everything 13 and above is coming out as per the web page eg , 13/14 or 15/15 . I am actually only wanting the first number or numbers to the left of the slash / mark , so col R2 might be 1/1 i want only the 1 to appear , 13/14 i want just the 13 and so on , they are only ever 1 or 2 digits ,
View 9 Replies
ADVERTISEMENT
Apr 27, 2012
Sheet1
DEPARTMENTLOCATION POSITION TITLEPOSITION NUMBERCODEKXNJCEO34500A3100KXNJCEO34501A3200DXDLMGR42001A5600DXDLMGR42002A5700NXNLCHIEF23001A1200
Data Range: A1:E6
Sheet2
DEPARTMENTPOSITION NUMBERLOCATION POSITION TITLECODEKX34500ANJCEO3100KX34501ANJCEO9100DX42001ADLMGR5600DX42002ADLMGR5700NX23001ANLCHIEF1200
Data Range: A1:E6
What I need is a formula (Not a VBA), is to reconcile both sheet2 and sheet1 ensure that the codes appearing for each position number in sheet1 gets updated based on the codes for each position number appearing in sheet2. So, for instance, the code for pos #34501A should be changed from 3200 to 9100 in sheet1.
The only issue with sheet2, though, is that the column number for position number could be different each time new data gets copied into sheet2 (thought the header information stays the same). I know that it can be done via Vlookup if the place of the column doesn't change each time, but I just
How to locate the correct column and row in a range of cells to search and then extract information based on certain conditions.
View 7 Replies
View Related
Mar 20, 2014
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.
View 4 Replies
View Related
Aug 11, 2009
looking for an equation or macro that can extract numbers out of a sentence.
Eg, 'There were 300 children in a school, and 10 classes of children' I need to extrcat the 300 and the 10 into 2 cells.
There may be more than 2 numbers in longer sentences. Is there anyway to do this?
View 14 Replies
View Related
Mar 4, 2014
Cell A3 has the formula: "=MOD(H16-H15,1)"
Cells H16 and H15 are time entries
If H15 = 12:00pm and H16 = 2:00pm, the result in A3 = 2:00
Cell A5 has the formula: "=A4*A3"
In cell A4, I enter a number, say "4", which produces the result "8" in cell A5
Now...I want to add to the formula in cell A5 to make it "=(A4*A3)+A4". The result I WANT in this example should be 12 but no matter what, the answer always ignores the "+A4" part and always just gives me A4*A3.
So the question is, how do I extract the result "8" from cell A5 as a number to which I can add (or subtract...or ANYTHING) other numbers accurately?
View 3 Replies
View Related
May 14, 2014
I have a column with codes coming after a # symbol and want to extract just the code from the cell. The code could be 2 or 4 characters in length. Some examples:
#AA abcdef
From this I would want the formula to return #AA.
#ABCD qwerty
From this, I would want the formula to return #ABCD.
The #Code should but may not always be the first word in the cell, so it needs to find the # symbol and then take the next x characters until a space is found. Note that the reason code may be the only text in the field, so a space may not be there at all. For instance,
#RR
Should just return the #RR.
Edit: This actually has become more complex now. The cell could be
#AA{text}
And for that, I would just want the #AA.
The formula would need to begin at the # and return just the next x capital letters.
View 9 Replies
View Related
Jun 25, 2014
We are changing management systems here and in order to do so we need to provide the names and addresses of over 400 different people. Our current management system stores people's addresses as a whole for example; 32, Example Street, Town, County, Postcode.
I cant extract just the numbers because I only need the house number and not the postcode.
After searching several forums I thought I had solved it with this.
Cell A1 is the extracted address from current management system reading 32, Example Street, Town, County, IU13 8TY
Cell B1 is =LEFT(A1, SEARCH(" ",O15,1))
Cell C1 is =SUM(MID(0&B1,LARGE(ISNUMBER(--MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1))*ROW(INDIRECT("1:"&LEN(B1))),ROW(INDIRECT("1:"&LEN(B1))))+1,1)*10^ROW(INDIRECT("1:"&LEN(B1)))/10)
That only gives me the number "3" though and I need 32.
Basically A1:A400 will be addresses as above and in cell B1:B400 I need just there house number at the start.
View 3 Replies
View Related
Jun 1, 2009
I need Excel to create Date's of Birth from following data in column A. If the data are 7 numbers long, I need to add a '0' to the start and have used the custom format of 0#######. This works fine but I need the data in column B to just display the first 4 digits. I have used =LEFT(A1,4) to do this, but it's not picking up the 0's which I've added.
View 11 Replies
View Related
Apr 30, 2013
Is there a way to extract a phone number out of a string of text. The phone number will always be expressed the same way (eight digits seperated by a space half way through) - 9999 9999. But the text before and after the number is random. Is there a macro to do this on a large scale? The method needs to be applied to hundreds of these strings, each on a new line.
An example of the string of text with the phone number underlined:
Alesdro 0 63PnantCey4281 5919 Aledio daSisaon' Brglry
View 7 Replies
View Related
Jul 4, 2014
I have an excel stock price template, where I need the current price to calculate the mid point if the chart.
This is the text I get from the webquery
Underlying stock: SBIN 2699.00 as on Jul 04, 2014 15:30:36 IST
I need a formula to extract the stock price '2699.00' only from this text.
The problems are sometimes its may change to underlying "stock" to "index", SBIN to RELIANCE or TATASTEEL, and the price some times two digit say 16, some times 6 letters say 150.05, or 8 digits say 15160.00
Formula, the text in B1 and need the price at A100.
View 9 Replies
View Related
Apr 27, 2007
I have a text field which contains multiple numbers ( reference #s and phone #s). I need to extract the reference # which will be either a 7 or 8 digit number. That number will not fall in any particular place in the string.
View 9 Replies
View Related
May 17, 2007
I am trying to plot data that I have read out of a PLC (via csv file) . The date/time data shows up as follows: 2007-05-14T02:15:00.020-07:00
In this example, the suffix .020-07:00 is extra information and causes problems with graphing the info. Also, the cell is a not a number value, and I can not force it to be a general, number format.
I would like to truncate the cell or extract the time out of it such that I can plot the PLC data in a meaningful "parameter vs time" graph.
View 4 Replies
View Related
Jul 14, 2014
"Type Mismatch error" in the macro below. I am trying to implement logic, where if value in column F, equals "1ma+89", then extract 89 only in column G. I also have other values in column F, where I have set specific rules for them, to extract numbers.
I have attached a worksheet with example data and highlighted red cells, represent incorrect output from the macro.
test2_14July.xlsm
View 4 Replies
View Related
Oct 2, 2011
I have a column of cells, each with a with text string that has three possible formats where I want to extract the total number of seconds.
The three formats are:
nnn hrs nn min nn sec (example: 1 hrs 42 min 58 sec)
or
nn min nn sec (example: 55 min 1 sec)
or
nn sec (example: 10 sec)
The result I want is a numeric value of the total number of seconds in the text string.
For example a text string of: 1 hrs 42 min 58 sec
Should give the result: (1 x 3600) + (42 x 60) + 58 = 6,178
View 9 Replies
View Related
May 10, 2014
I have a cell containing alpha and numeric. I would like to extract the highest number in the cell
the cell looks like this
the answer I would like is
====G99876554321000
9
T1111111100000000
1
I am using Excel 2010
View 3 Replies
View Related
May 20, 2008
10/05/0808:30:00 AMValid Card EntryLamDoor 1
10/05/0808:31:00 AMDoor Leave Open Door 1
10/05/0808:32:00 AMDoor Closed Door 1
10/05/0808:41:00 AMValid Card Exit Yap Door 1
10/05/0808:46:00 AMValid Card Exit Lam Door 2
10/05/0809:14:00 AMValid Card EntryLam Door 2
10/05/0810:18:00 AMValid Card Exit Lam Door 2
10/05/0810:19:00 AMValid Card Entry Yap Door 1
10/05/0810:40:00 AMValid Card Exit Yap Door 1
10/05/0810:42:00 AMValid Card EntryLamDoor 2
11/05/0808:30:00 AMValid Card EntryLamDoor 2
11/05/0808:20:00 AMValid Card EntryYapDoor 1
11/05/0810:20:00 AMValid Card Exit Lam Door 1
11/05/0811:40:00 AMValid Card Exit Yap Door 1
the above is the data i need to work with (dates are in dd/mm/yy). i need to find a way (a macro, preferably) to extract the date and time corresponding to one person, and paste it into a new sheet, e.g. at sheet 2 (renamed to "lam") should be
10/05/0808:30:00 AM 11/05/0808:30:00 AM
10/05/0808:46:00 AM 11/05/0810:20:00 AM
10/05/0809:14:00 AM
10/05/0810:18:00 AM
10/05/0810:42:00 AM
View 10 Replies
View Related
Jan 23, 2013
I have a spreadsheet full of data and I need to extract only those lines of data which can be identified by the last three letters in a cell. I am adding an example spreadsheet which highlights the rows I am trying to extract based on the contents in column B but only where column B ends in KY.
Example data extract requirement.xlsx
View 5 Replies
View Related
Jan 5, 2009
I have two exported lists of data to compare from my newsletter program. One is the list of email addresses that I sent the newsletter to and the other is a list of the bounced email addresses.
Question: How can I extract the bounced emails from the list of sent email addresses?
In other words I want a list of clean, sendable email addresses.
View 10 Replies
View Related
Nov 18, 2013
My query is , I require the UCF column for the ID having maximum number (last five digits) of UCF column for the respective year column values
IDUCF Yr
559589876543212610007971212
559589876543212610000333712
559589876543213610004703013
563829876543212630000323912
My answer should be like below (or) having an additional column saying Active/Inactive.
IDUCF Yr
559589876543212610007971212
559589876543213610004703013
563829876543213630004703113
563829876543212630002782312
View 1 Replies
View Related
Jul 30, 2013
I am trying to extract data from a website:
[URL] .....
I looked at the source code of the website and realized that if you notice (above) that the variables listed in the link (i.e year, month, day) are exactly what i need to change in order to get the data for a specific date. how can I accomplish this using VBA. so say I have in on an excel sheet year in column A, month in column B, and days in column C (time interval is constant so we don't have to worry about stime and etime). and i run the macro and it loops through each row taking year,month,day for all rows and saving the data as .csv or xls files?
View 1 Replies
View Related
Feb 19, 2014
how I can extract data between two strings?
Example: My string is: -_creativename-_spongecell-_creativesize-_300x250-_creativetype-_spongecell-_
How can I create a column that contains everything AFTER 'creativename-_' and before '-_creativesize-_'?
View 11 Replies
View Related
Aug 4, 2014
I have created a table of data with Serial No, Name, Age & Common Friends field. Now i need to extract all the data (complete Row item) where the condition matches a certain criteria. Basically a formula or function which will say what are the names of people along with all other corresponding fields (Age & Common friends) who have common friends as "Jill" or "Jack" (For example). Note : I cannot use filter option and copy/paste the filtered data in new sheet as i am working with thousands of data items. Due to the nature of report software/data extraction report, multiple data items appear in single cell only (Multiple friends name appear in single cell).
View 3 Replies
View Related
Jan 7, 2009
I have a problem with VLOOKUP not extracting the data I need from 1 workbook.
I want to populate 4 different sheets in the "108" workbook with data from the "1st" workbook. I've uploaded the workbooks to show the formulas I have already. The data in "1st" workbook changes daily and can have upwards of 2000 rows. I've simplified the list for the example.
In the "108" workbook I want to fill in the first three columns with the formula down to about row 500.
I've tried different formulas to do this using EXACT, MATCH, and VLOOKUP but to no avail.
View 9 Replies
View Related
Jan 15, 2009
I have a table of data on 20 employee performance stats for my department. It is in the format of col A names and corresponding values for multiple skills and performance data from Col B to M in rows. I have a worksheet for each employee within the workbook. What I would like to do is to be able to extract the individual row of data to each employee sheet. I have played with OFFSET but can't get it to work other than in a column..
View 9 Replies
View Related
Jun 18, 2009
I have table that has a list of agents (Colum B) and then corresponding appraisal dates; 3 Month (Colum C), 6 month (Colum D) and annual (Colum e).
What I would like to be able to do it to have something that looks at the table and tells me all the 'appraisals' that are due the same month we are in, and the date that it is due, as well as the 'agent' that is due the apprisal.
I have played with the INDEX MATCH formulas and also pivot tables but have not found a solution, is it something that is going to require VBA or is there a simpler solution?
View 8 Replies
View Related
Mar 25, 2008
Let's say 1 Feb starts on week 5, 1 Mar starts on week 9 and 1 Apr starts on week 14. As such, feb has 4 weeks and march has 5 weeks. I have a spreadsheet of data on a weekly basis, and i want the data from week 5 to week 8 to be under feb and data from week 9 to week 13 to be under march. The data is to make a graph. Is is possible for excel to be linked to the calander so as to detect which month has 4 weeks and which month has 5, depending on the 1st of each month?
View 11 Replies
View Related
Nov 5, 2008
I have many company-specific Excel files containing given data fields such as company name, company address, commodities supplied, etc. An example is attached here under the tab "Company Profile". I would like to extract all the information from the different "Company Profile" Excel files and put it into a more usable format, as shown in the attached tab "Results," where the company data runs horizontally across.
I tried to record a macro to do this ("CompanyProfile" -- included in the attached file), but I am not able to make it do what I want it to do. I also do not know how I may be able to capture the information in the "check boxes."
View 2 Replies
View Related
Feb 1, 2010
In one cell i have a dropdown. Depending on what is in that dropdown, i want a table of data to be written. I'm not too hot with pivot tables but my understanding is that is predominantly to do with counts. This is more of a multiple vlookup i guess. I want to pull from a list of data (3), all the cases matching the dropdown (1) and deposit into a list (2). What is the best way of doing this? I don't want to use filters. See attached.
View 2 Replies
View Related
Aug 4, 2009
I am using INDEX functions to extract up to 2 items of data which I now need to extend to upto 6 items.
The spreadsheet shows a list of staff initials and a day by day work rota of (M)ornings, (A)fternoons, (N)ights and (O)ffs. What I am needing to do is to identify only those staff who are rosterred Off and to place those intials in the two sets of colored cells in rows 5 and 7.
The attached spreadsheet, Tester1.xls shows the 2 existing INDEX functions in use. These can only detect the first occurrance of "O" and the next. I now need to be able to extend this functionality to cover all "O"'s in both section 1 and section 2. So, eg if 12 Aug is input to B15, I can only select IB and GG, but I now want to be able to see MG in the next blue cell.
View 3 Replies
View Related
Nov 9, 2011
[URL]...
This entire link is in one cell i would like the data Iowa_Health_System
What formula can i use
View 2 Replies
View Related
