I have a spread sheet with following:
Name|Address|Home Phone| Cell Phone| Status|Project Name|
I want to select name and extract only home phone and cell phone nos.
Please help me with a code. I have about 2500 entries.
This how it looks like, i used this code (see below) to get the amount if there is a word "ins" (martin's code)
IF(AND(ISNUMBER(SEARCH("ins",B47)),NOT(ISNUMBER(SEARCH("insp",B47)))),LOOKUP(99^99,--("0"&MID(B47,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B47&"0123456789")),ROW($1:$10010))))+0,""))
it works pretty great, but when things getting better, i've encounter this data: 12,300 ins 09-10 flood
so what i did, is to put another if function, like this: ..
Does anyone know of a formula that will take an expression such as
"C1-5" in a cell, and produce five separate values in 5 cells:
c1
c2
c3
c4
c5
Even a starter would help, like how to extract values from "1-5"
Ultimately I'd like to use this for values that can have up to 3 digits following the letter (ie, C99-112.)
I am trying to transfer data from some appointment based software into a spreadsheet .
the data comes out as one row For example ,the following is the contents of cell A1: 06/06/2014 09:00 AM - 09:30 AM Patient: John Smith
What I would like to do is extract one column with the date ,one column with the patient forename and one with the patient surname.
I have tried various combinations of =RIGHT(A1,LEN(A1)-FIND(".",A1)) etc etc but cannot extract the data I need
I am trying to do is extract the volume size of products in 'ml'
from 10k plus products from a description field cell.
this description field could also contain the weight of the product in grams
so I cannot just do a search for a numeric string ,
it has to be associated with the milli-litres statement .
is is possible to do a sort of ' *ml ' search and then select and copy to another cell ???
I have a sheet where each row contains the name of a customer and should also have an 8 digit account number, all in one Column. Some do not contain the account number. Is there away of extracting the account number to a new sheet/column? The account number will always be 8 characters and will contain letters and numbers, the letters in the account number will always be UPPERCASE
View 7 Replies View RelatedHow 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'm using Excel 2007 and I'm looking for a way to extract certain records/rows from a table and to have that data copied to another area of the spreadsheet. I actually want to extract Invoice amounts according to a specific account number so that I eventually end up with all the invoices for a particular client on another tab so that they can be printed out as a kind of client statement.
I know there are ways to filter and then copy the records/rows to another tab but I want it to be automated as far as possible. What I really want to do is to "pull" the records into a new tab by using some kind of formula in the cells where I want the data rather than having to copy it there. I know how to sum data for this kind of thing but what I'm really struggling on here is being able to "Display all the records in a seperate area" first before I eventually add formulas to sum.
I don't mind the records being coppied if the process is automated but don't really want to get into macros as writing macros isn't my strong point.
I've tried using "VLOOKUP" but it will only return values for the first data item it finds (Account number in my case), whereas I really need it to continue to return values for the data item it finds if there are multiple instances of it!
I have a large spreadsheet which contains multiple rows of rate data by employee. I have sorted by effective date with most recent date on the top to get the most recent rate of pay, however, is there a way or formula to be able to identify or extract the previous rate of pay after that? I have included a sample below. There are different #'s of rows of rate of pay based on how long someone has been in company, as well as different rates of pay and effective dates.
Employee #
Name
Rate of Pay
Eff Date
655220
John Smith
$ 99,000.00
1/1/2014
[Code] ..........
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 RelatedI 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.
I'm creating 10 'categories' for Column "C" that I plan on using drop-downs to select from. Column "H" has a value tied to "C". What I want to do, is have Excel SUM all of the values in Column "H" where the Category in "C" is a specific text value (e.g. say a category is "hardware", I want a cell with a formula that sums all of the values in "H" where "C" is "hardware".)
View 8 Replies View Relatedi'd like formula to find value above and below specific cell
for ex
i have value in cell c5
i want formula helps me to find
values above c5 in cell c4,c3,c2,c1
and
values below c5 in cell c6,c7,c8,c9
as-18542-1
sp-25641-1
as-35678-4
u can see in that no last part like this.-1,-2,-4
i need to remove that part.i have lot of no like this.i am going to do this manually it will take long time.pls some body can make macro or something for this.
answe should be like this.
as-18542
sp-25641
as-35678
I want a range to be filled based on 2 cell values in my sheet. The autofill range should be filled based on the values in the input range. For instance in cell A1 I place the value 1 and in cell A2 I place the value 10, then the macro should automatically fill in the numbers from 1 to 10 in another column. If I change for instance the input values to 5 and 15, then the autofill range should fill up the numbers from 5 to 15.
View 4 Replies View RelatedI'm looking for a formula to sum three adjacent cell values in a specific row and then sum up all of the rows.
A B C
1 0 2
3 1 2
4 4 0
For example, the value for the above would return 17 = [(1+0+2)+(3+1+2)+(4+4+0)]
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!
I want to get each distinct values from a range (say, c1:c20) and put them into an array for further processing.
View 3 Replies View RelatedI have a list of data:
2
140
1
660
10
140
0.92
660
2
130
0.18
660
4
510
0.44
820
4
510
I want to have a formula that finds the sum of the values in Col 1 (Qty) for the rows that equal, eg: 140, in Col 2 (Product) So that I can have a list of Products of the Qty that relates to each product. (there are products in increments of 10 from 10 to 920, that is, 92 products)
I'm finding it hard to explain what I am trying to do (which may be why I can't find a solution through Google), so I have an example. I am using a much larger spreadsheet than this sample(18,000 rows)
ID
First_Name
Last_Name
Company
[Code]....
I want to highlight rows if there are multiple instances of ID and # values - I included a column at the end showing which rows in this data set would be highlighted.
I need to keep track of tardy occurences, but I only have to consider occurrences that have occurred in the past six months from the current day. Column A contains the names of the employees, Column C thru IV contains daily dates beginning with 1/1/2007. Column B contains a CountIf function to count the number of times T appears in columns c - iv.
View 9 Replies View RelatedI know that there are programs out there that will convert a PDF into excel, but what I need to know is, is there a way for excel to access values (numbers) in a PDF and then use those values in a worksheet?
So, if I have a table in a PDF file - containing part numbers, descriptions and quantities ordered, I want to be able to extract the part numbers and QTY, and have them populate an excel table?
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] ......
where i have a products list and respective pictures in a folder. What is should do is to get the relevant pictures appear on the screen when i select the relevant name.
View 1 Replies View Relatedi have this macro that saves into specific folder dependant on cell values and it works spot on with the exception that my po numbers are like st010,st011,st012 etc etc but when it saves it doesnt have the st in front. ive tried numerous ways but to no avail
View 5 Replies View RelatedFrom 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.
I have certain values relating to a single person spread out in various worksheets and I need to get that in one sheet.
eg: For "NAME xyz" I need the values corresponding to "apples" in as many 10 sheets. Similarly for about 20 different things for about 300 names.
Sample Data:
Col A: Col B:
CV01 01
CV01 01B
CV01 02
CV01 08
CV01 03
CV01 10
CV02 02
CV02 02B
CV02 03
CV02 10
CV02 01
CV02 09
CV03 06
CV03 06B
CV03 04B
CV03 05B
CV03 08
CV03 09
I need to generate/extract a "list" of all the values in Col B. that "belong" to each value in Col A. So, if my criteria is "CV02," I need the list to be 02,02B,03,10,01,09 (with each value in it's own cell)
I've tried the "VLOOKUP" function, but it only returns the last value for each criteria (example =VLOOKUP(CV01,A1:B18,2) returns "10")
I've searched these forums, but I'm not really sure what search criteria to use...
This is my qns
cell A1 having <txt>NEWM</txt>
cell A2 having <txt>RSQ</txt>
I need to display NEWM in B1 and RSQ in B2....
extract the highest top 5 values from one column in a filtered table with hidden cells. How can I do this without using advance filter or a pivot table in excel 2003 that will show only the visible cell values and not the hidden cell values
