if it is possible to create a formula to just extract numerical data. It is formated in text or sometimes general format.
Example
John Doe - 1202939402093
109230920933 - Jane Doe
Is there such a formula that will read the cell, and extract the numerical part of the cell and place it in another cell?
Cell is input as 01.01.2007. Using =MONTH
formula I am unable to get 1.
Any thoughts on how to extract the month from the middle
digit.
I have a sheet with thousands of rows of ID's which are made up of a series of letters, numbers, underscores and or spaces. Within the sheet there are numerous duplicates so I need to able extract the numerical element, which is the key data to be able to identify the duplicates.
The numbers I need to extract are mainly 6 and 7 digits long however don't always sit in the same place within the ID. I don't need the 3 digit number sequences, just the 6 & 7 number sequences
e.g
AB_123_456789_Jones
CD_131_7654321_Smith
AB_123_Jones_456789
I've found a formula that strips out all the numbers, however it also included the 3 digit numbers which I don't want. I've also used Text To Columns to break up the ID across a number of columns however that leaves me with 2 and sometimes 3 columns where a 6 digit ID appears
Is there a simple way to extract the number if it's a certain length? i.e. 6 or 7 digits long
I have a *.bmp image file. It is gray scale only. It amy be up to 1000 x 1000 pixels.
I want to extract the numerical value of each pixel and deposit the values into an Array for further processing.
produce graphs/charts based on inequalities monitoring information - gender, nationality, age etc. I'm capturing info from several events, one sheet per event. Had a look at COUNTIF but not sure it's what I want and looks like I'd need sub-sheet for each? Capture.PNG
View 7 Replies View RelatedThis should be easy but after searching "consolidate" all that I could find was multiple sheets and complex questions.
I have 5 data columns that I need consolidated into 1 single column. I've tried the consolidate funtion but it appears to only work with formulas and numerical values (unless I cant find the proper command.) I've also tried paste special but it populates the cells following.
I need 5 cells word values pasted into a single cell
I have imported data and numbers from a website and would like to sort the data in numerical order. Excel wants to sort the data 1,10,2,20... in that order. I know from past experience with an older version there was a way to convert the numbers so they can be sorted in the way that I want.
View 2 Replies View RelatedI need to create a table of non numerical data that I can refer to in a formula, which will compare two pieces of data (both also inside this table), and based upon which is "higher up in the hierarchy" - the cell will display a certain result.
Basically it will be an IF function using non numerical data that has a numerical value / "rank" attached.
Specifically - I need to allocate values to this list in cells A1:A5...
EUR
GBP
AUD
NZD
USD
....where EUR is highest ranked and USD lowest.
Then in column B and C will be listed each of these values but in various combinations. In column D I will build simple IF functions to show the highest ranked in each combination. eg B1=EUR C1=GBP D1=IF(B1>C1,B1,C1)
how to process this non-numerical. I tried to use formulas but it wasn't fruitful. refer the table and.
Name
English
G
Maths
G
Science
G
History
G
Aggregate
Result
Achievement
[Code] ......
****The aggregate derived from the grade of the subjects , where each grade will be given a pointer and the average pointer for all four subjects will be considered as aggregate :
Pointers for grades
A =1
B=2
C=3
D=4
E=5
This is how the aggregate for Alex should be counted:
English A = 1
Maths C = 3
Science B = 2
History A = 1
*****aggregate = (1+3+2+1)/4 =1.75
The result column be showing Pass/Fail. If the student fail one of the four subjects he is considered as Fail.
**Grade D and E =>Fail
The Achievement Column should be indicating the numbers of As, Bs and Cs
for example: O6= A(2),B(1),C(1)
Need to sort my data in column A which contains a long list of numerical data. To do this manually using copy and paste would be easy, but not viable because of the large amount of data.
The attached PDF file illustrates the data. I wish to replace each date of dirth entry in column A (one D.O.B. in each cell / row, expressed as YYYYMMDD) with the client identifier number (format 937XXXX) a couple of cells above. And at the same time delete the row containing the client reference (or its contents). The data in column A should retain the same relationship to the data in the other columns (B,C). it may require a short piece of VBA code.
Need the code to make a graph (bar diagram) based on the only the rows with numerical data in a column, where there is also blanks and NA. The graph I would like to make and the data-structure are shown in the file Graphs.xls. When updating the graph later on the cells with data may have changed (i.e. new rows with data may have been added)
View 4 Replies View RelatedWhat would cause a pivot table to turn numerical data into a letter?
I have a field, cost center, that has about 15 different possible values in it that look like this: 141350, 141360, 141370, 141380, etc. When I compile the data into a pivot table and try to sort on pivot table everything looks normal, except that 141350 is returned as 'R'. If I change the value to anything else, it is fine, but as 141350 it's changes from numerical formating to a single letter.
I'm sure something was accidentally set to do that automatically.
I'd like to find out the formula where numerical valued between 1-2000 equals True and numerical under 0 equals False. Examples are in the attachment.
View 3 Replies View RelatedI would like to create a chart with stacked columns. Usually, the data on the Y-axis is some kind of numerical data, such as budget. I would like to have the Y-axis have non-numerical data (= nominal categories).
Example :
Take the following data :
category | productname
red berry
red carrot
green cabbage
green apple
The ouput format i want is a chart which shows on the horizontal axis the categories red and green, and stacks in each category the right productnames on top of eachother; preferably each productname should have a different colour and showing data labels (e.g. "1", "green","apple" could be 1 specific data label).
I don't know how to achieve this, because if you display data as "count of productnames", then automatically you get something like a block with value 2, which doesn't show the different productnames inside that block.
Can the desired chart be made with Excel somehow?
I am quite inexperienced with Excel and I am struggling to understand how should I set up a formula, within the data validation function to achieve the following:
I need to restrict a certain set of cells, for people to be able to input only the following values: "1", "0.5", "A", "H", "0.5A" (or 0.5a), "0.5H" (or 0.5h)
And I would need the data validation tool not to include blank cells.
loop that can calculate the median of dynamic ranges. I need to run through all columns and for each column find the the ranges (there are more ranges and they are seperated with a blank row) with numerical data and then calculate the median and thereafter continue down to find the next range with numerical data in that row and so on.
View 4 Replies View RelatedI'm having issues with Excel's 2010 conditional formatting. Seems easy to use, but I'm trying to highlight values based on 2 columns of numerical data. Example:
Column F:
6
6
14
Column L:
3
NA
17
I would like Column L to highlight values that are greater than Column F in green. If they are less than Column L then highlight them in red.
Seems I was able to do this with Excel 2003, but I don't understand the 2010 version.
I am trying to get excel to search a workbook/(or worksheet if easier) for a matching unique value and fill in its associated data. My first workbook has the SKU (A) filled in but not the UPC (B). My second workbook has both the SKU (A) and the matching UPC (C) filled in.
I need to take both workbooks/(worksheets), compare the SKUs, and if a matching SKU is found, extract the UPC from Workbook 2 and fill in the UPC field in Workbook 1, and if no UPC is present in Workbook 2, then it leaves the cell in Workbook 1 blank.
I am looking for VBA code to extract data from each tab into master tab based on data.
I have 3 tabs (inputs)
Tab1 (Dept A-NAME) (RANGE B4:I7)
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
1
A
2/25/2014
Customer 1
10
1
3
ABC
[Code] .....
Tab 2 (Dept B-NAME) (RANGE B4:I11)
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
1
B
2/25/2014
Customer 3
10
1
3
RTY
[Code] .......
Tab 3 (Dept C-NAME) (RANGE B4:I7)
Is it possible to run vba code to get below result in new tab
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
Result
1
A
2/25/2014
Customer 1
10
1
3
ABC
Dept A
[Code] ......
Condition here is date 2/25/2014, is possible when running code message box pops up to ask date, when we give conditional date it extracts those dates.
Excel -2007 & Windows 7
So I have this problem in excel with comparing 2 columns.
Basically, I have 2 columns(a &B) that I need to compare with one another and find out the matching data. I am trying to use that matching data to enter in our reports.
Both columns might have duplicate items because I am dealing with premium numbers.
Formula to compare these 2 columns and find the matching data and extract it to a separate column. Keep in mind, i am dealing with almost 20,000 lines of data.
I am trying to extract unique Data from the information in the output data in the file attached after i have extracted data based on criteria from the database sheet. i have noticed that because i extract data from the database sheet and from there i perform the extraction of the of the unique data , it is not able to extract unique isser name form the output sheet.
View 1 Replies View RelatedI have a workbook that has ben imported into excel and I need to extract the Cabinet sizes (red text) from ColumnC On Sheet2 leaving out the rest of the info. I have a button on the home sheet that has the code that I am running to generate this report it will do most of what I want but I am stuck at extracting the cabinet sizes.
The data is coming from the SheetComponetListing worksheet and going to Sheet2. I have manually created the end result that I am looking for on the CabinetSize worksheet. [URL]....
I have a table: A1: K28879
I need only certain rows to be extracted and placed on same worksheet or a seperate one BUT
The rows to be extracted are under a heading above it which that says 9 number and Count eg: 123456789 Count. (Count is alwatys there the number vary however its always in column A)
EG:
45621234 Count
data data etc..... (i need this row)
...another row
...another row
..etc
456345678 Count
data data etc.. (this one)
more data... (not this row)
etc..
The first row under that heading needs to be extracted only.. some under that heading might have 1-15 rows it varies...
I have 4 columns of numbers. How to randomly extract a certain set numbers from each column. Then display the results from smallest to largest. I provided a sample worksheet..
View 3 Replies View RelatedI'm running a small messenger company and I've got a geographic area divided into five zones: 1,2,3E,3W,& 4
Deliveries within 1 zone will be charged for one zone.
Deliveries from Zone 1 to Zone 2 will be charged for 2 zones. Etc.
I've got a list of zones in my From column and also in my To column.
I need to write a formula that will give my customers the Zones Charged in a seperate cell.
I learned how to do something like this back in school, but don't even know what to call it anymore. Maybe it's some kind of comples IF function.
FREE Deliveries to anyone in Long Beach, CA to the winner!!!
how to extract all members of group of data (column) - for example data is (2,4,2,5,6,6,2,6,5,5,2) and to get as result only members of group (2,4,5,6)?
View 6 Replies View RelatedI have a string (as below - Call them A1:A4) which I would like to seperate into 4 columns (Call them B1:E4).
I have successfully seperated the first part using MID (It's always 5 digits) but the second part has a varing length which then impacts on the third and fourth parts of the string.... Any ideas?
87261 WIMBLEDON 10:08 10:10
87169 NEWMALDEN PASS 10:13
87171 SURBITON PASS 10:15
87177 HMPTNCTJN PASS 10:16
To add to this I am using the POCKET PC version of Excel which does not have all functions so at the moment I am limited to which functions I can use (Can you add functions to the PPC?).
I want to extract data from Serrial number 2 columnwhich are not match to Serrial number 1 column and past into in third column
View 5 Replies View RelatedI have 2 columns. One is the years and one is the the associated data.
I need the associated data(BP values) to be sorted in years like shown in excel sheet attached.
I have data in ms excel and in column "F" is the Qty
If the Qty is Less then 40 then extract the data and put into the sheet "Less then 40"
I have attached sample excel file : Extr.xls