I have a phone list set up as LastName, FirstName PhNum all in a single column. The problem is, some last names have a space, some phone numbers are in the xxx-xxx-xxxx format while others are just an extension like xxxxx.
Example:
Smith, John 519-123-4567
Van Smit, Joe 12345
What can I use in a formula to extract the numbers so that I can end up with 2 columns, one with names, the other with numbers.
I have 5 different groups for that i have to make XY scatter graphs and my data looks like this
XY
125
13
156
145
134
234
248
260
2102
2105
3125
3148
3198
3298
3245
4386
4369
4234
4465
4898
5902
5907
5345
5234
5209
now i want to make 5 different graphs one per each group. Now i am making them manually in excel but it is taking lot of time to format each graphs to get unique graphs. I am attching sample file and graph for better understanding.
A
B
c
d
[Code]....
So the data I put in Is Cells A to D the reast are calculated for me. so for example E2 is looking down column D to see if the number matches the number in A2, If any of them do it will put the name in the corresponding cell in E2 (Or you ca use a different cell if you prefer) so in this case D3=A2 so B3 is the answer also in the cell next to it I need whatever number is the cell next to the answer so in this case C3
I know I can use text to columns to do this. I have horse results listed in column A as follows 3.5L (i.e. 3.5 lengths), 4L and so on. Every number ends with an L. I want a formula in column O that just gives me the number as below:
3.5L 3.5 (in column O)
4.5L 4.5
and so on.
I need the formula to separate the numbers with commas in a cell to other columns as shown in sample.
This data comes from internet and automatically updating certain intervals, so the text to columns option has no use.
I receive an extraction from AutoCAD that lists the electrical devices in a drawing. I don't have any problems extracting the letters. I have a problem extracting the device number and the device number extension.
The device label extraction is similar to this:
DCM1005-1
DCM1005-10
DCM1005A
MTR1005-1
MTR1005-10
MTR1005A
I want to create 3 columns from the device label: (I separated the column with commas)
A1, B1, C1, D1
DCM1005-1, DCM, 1005, 1
DCM1005-10, DCM, 1005, 10
DCM1005A, DCM, 1005, A
MTR1005-1, MTR, 1005, 1
MTR1005-10, MTR, 1005, 10
MTR1005A, MTR, 1005, A
I'm trying to separate text from numbers into two separate cells...
Essentially, I would like the users to copy and paste data into Column A, as seen below. Then, hopefully by formula separate the text characters into Column B and the numbers into Column C.
Input: Output 1: Output 2:
Col A Col B Col C
Wells 123 Wells 123
Wells 1234 Wells 1234
Wells Fargo 123 Wells Fargo 123
Wells Fargo 1234 Wells Fargo 1234
Wells Fargo Inc 123 Wells Fargo Inc 123
Wells Fargo Inc 1234 Wells Fargo Inc 1234
Ideally, I would like to do this with a formula...
Excel 2010, I have the following list (showing part of it):
Name
T-Shirt Sizes
Nathan
S
[Code]....
The aim of the list is to see what Sizes each and every person needs and then count how many T-Shirts are required for that particular size. The list is over a 100 entries long so counting it manually isn't really an option as it's time consuming as well as errors might arise.
Thus, I've been trying to use the IF statement with the following logic but to as no avail
IF Size in Column is XS
Add 1 to a particular Cell
IF Size in Colum is S
Add 1 to a particular Different Cell
and so on for the other sizes...kind of like a counter for a for-do-loop.
I'm trying to make a spreadsheet for work so when im not there they can just enter the day for example sunday and what they have and then it will show them what they need to prep
View 4 Replies View RelatedI have a big list with names, money spent and date. I need somehow with a formula to do:
Take all the money spent on some specific day from a specific person an put it into a field.. and this for every date of the week and for everyname... Im gonna upload a photo.
Name
Money
Date
Daniel
5
01.03.2014
[Code] ........
I need the list of the left to produce me list of the right somehow, at least just the sum of everything spent everyday.
I have two columns in my excel sheet, the first column has the Second name, First Name and the second column has the Date of birth.
Now what I'm after is two columns really, the first column should show the Furst name Second Name (space in between with no Comma) and the Second column should show the First Initial from the first name, the first initial from the second name and then the date of birth.
I have a column of first and last names that sometimes look like this: Smith, Michael D and other times look like this Smith, Michael (no middle initial) I would like to separate them into two separate columns, one for First name and the other for last name. I have no use for the middle initial so that can be ignored.
View 4 Replies View Relateda smart formula (no VBA) that will reverse the order of a list of names - parsing into separate columns.
The formula needs to be able to recognize/distinguish that if a comma(s) is present, then the names are listed as last, first middle. If there is a period this means the name has a middle initial or that a suffix is present. So,if the names are listed as last, first and middle then in the next columns will have in the proper order first name, middle name or initial (keep the period) and last name (loosing the comma of course).
If the original name order is in normal order - first middle and last then the formula should just pass through without any change. Would need to preserve generational titles such as Jr., Sr., III etc. Not sure where the best place for the generational titles should end up. Probably as part of the last name. See examples:
Col ACol BCol CCol D Col E
William J. ClintonWilliam J. Clinton
Churchill, Winston Leonard Spencer Winston Leonard Spencer Churhill
Bates, JohnJohn Bates
James Knoll PolkJames Knoll Polk
Taft, William H.William H. Taft
Carter, James, Earl, Jr.James Earl Carter Jr.
Rockefeller, John Davidson, IVJohn Davison Rockefeller IV
Gates, William, H., IIIWilliam H. Gates III
St. James, Michael, Ken Michael Ken St. James
CherCher
I tried using many variations of FIND, SEARCH, LEN and searching all the Excel boards to no avail.
In order to avoid embedding 12 if statements, I want to take an average of a selection of cells based on what month it is. So I have a "now" function, and then a month function referencing the "now" cell so that I get the current month as a number 1 through 12. I have a row of expenses (C14:N14) and I just want the average from January up to the current month. The reason I can't take an average of the entire selection is because the users don't want to have to enter a 0 everytime there's nothing, they'd like to leave it blank, so the sheet needs to recognize what month we're up to. And the average function in Excel only takes an average of the data that is present, not the entire range. For example, if the data in the first cell was 15, second cell was 15, and third cell was blank, the average function returns 15 and not 10. So what I need is, for example, if it's March, the average will calculate =average(C14:E14) and so on. Any ideas? Maybe it's a simple change of formula instead.
View 13 Replies View RelatedThis is where you introduce yourself? I make lists for my gf's dad's company using excel. One of my most excruciating tasks is to separate the first and lasts names from one cell into two cells. Does anyone know a quicker way to do this other than manually? I have 10,000 names to do and it's going to be hours if I do it by hand.
View 10 Replies View RelatedThe database that I'm working on divides patient names into three separate columns: lastName, firstName, midInit. My department has several old spreadsheets that have data my boss suddenly wants included in my database. The trouble is, these old spreadsheets have the patient's last name, first name, and middle initial entered into the same column.
There are at least 200 hundred names in these spreadsheets. I absolutely do not want to go back and separate the names into separate columns by hand.
Is there any way to get Excel to take the names entered into one cell and split them into three cells?
I've attached a spreadsheet example that shows what I need to do.
I have a list of names in a Word doc that is set up like this:
John Doe
Jane Doe
Doug Birch
Mary Birch
Is there a way to copy that list and paste it into Excel and make each name go into its own cell.
Example:
A1 John doe
A2 Jane Doe
A3 Doug Birch
A4 Mary Birch
I have a file with list of names in the attched file.They are not in the same format coming from the source in my company. I need only the names in the separete column without the dates and the commas. The text is not in the same format. Looking at the data, can someone help me make a formula to separate only the names from the the entry.
the data is huge and it takes lots of hours to clean this data.
I am attachign the excel file for reference.
Basically I have in the first column, different products (lets call them a, b,c and d). in the second column is a counter which starts at 1 and continues counting down the column.
In the third column I need to identify if it is the last time the product has been run. i.e there are no more duplicates of product a further down the 1st column.
I have attached an example : example.xlsm
I'm in need of some VBA code to control the updating of information from 16 active workbooks to a master workbook and vise versa. I would like it on command as it slows down the use of the workbooks when they calculate after every change of data as they are being used. These workbooks are connected over a company intranet and are used 24/7.
The desire is that cells in the master workbook will read specific cells on each of the other workbooks and complete a "map display" of the data. The data is not in a column or row range side by side but scattered across the sheet to form a map when data is entered.
I've tried using code to open and close the workbook with on time events but that hasn't worked.
In my spreadsheet, I have data for each sale in each row. Column C is the buy price, and column D is the sell price. I need column C to be subtracted from column D to get the profit into column E so that I can AutoSUM any cells in column E for total profit over any time period. The problem I'm having is that in columns C and D, I have either a dollar sign in front, or the letter m following without a space (ie $45.00 or 150m). Many cells in column C also contain another word following the buy price, and many cells in column D have a date following the sell price (ie $32.00 12/11 or 42m buyout).
Examples of how my spreadsheet looks:
A1: 12/13/12 B1: Item #1 C1: 100m D1: 152m
A2: 12/14/12 B2: Item #2 C2: $20.00 D2: $35.00
A3: 12/15/12 B3: Item #3 C3: 90m bid D3: 120m 12/16
So looking at my example above, I would need cell E1 to automatically show "52m", cell E2 "$15.00", and cell E3 "30m"
How I can get a formula for all of column E that will give me my profit for both $ and "m" for each item (row). I have tried the SUM function but just keep getting the #VALUE! error, it does not ignore the text like many people say it should.
formula to calculate how many cells in a column contain data of any kind. The data might be a number, a word, or even a symbol, such as "):>:)" for example. The range will change from time to time. For example, one day it might be a2:a4956. A few days from now I might delete a few rows from the top, such as rows 2 through 26, so then the range would be a2:a4931. Then I might add rows at the bottom with data that I need to be counted. I dont want to have to change the formula evertime I add or delete rows with data. I do not want the formula to count a blank space created by the space bar as "data." One formula I used sorta worked, but if the cell appeared empty, but had been cleard out by pressing the space bar instead of the delete key, the formula counted it as a cell with data.
View 9 Replies View RelatedI am an architect and recently I was required to do some extensive calculation relating to water requirements and toilet requirements for a building that i was designing. i created a excel file, with a basic vlookup function to put certain values in the cells that are pre-defined for a particular building typology (mostly created by governmental bodies as guidelines). for example, when i choose a particular type in column E, column F and G fills up automatically. now this table also gives me occupant load based on what is the area i enter in column D.
The problem is that I want to automate the calculation of toilet requirements. Now, each typology will have a different formula to calculate WC/Urinal/washbasin.
1. there will be 17 different formula - one for each typology (as shown in sheet 5)
2. Column j will calculate what is the number of WC required based on 2 criteria - first id what is the typology and then looking what is the number of occupant.
The problem here is for example, if in E9, selected typology as "offices", the formula of offices typology will be used in J9 and occupant value from H9 will be used but if i have E18 also selected as offices typology, the same formula will use occupant value mentioned in H18 instead of using H9.
Hence, excel will first have to look what is the typology selected. based on that, it will load formula - out of that 17 different formula - specific to that typology and finally it will refer to corresponding row for occupant load and will use it in the formula to get the result.
I'm using a formula to lookup names within text and return that name to a separate column if it's found.
The formula is =INDEX($E$1024:$E$1026,MATCH(FALSE,ISERROR(FIND($E$1024:$E$1026,E1010)),0))
I understand index and match functions, but I'm confused regarding the use of FALSE as the lookup value and the iserror(find( usage.
I was working on a chart to calculate my Amazon seller rating to see how many more perfect order I need to hit my next target grade. Here is how the calculation works.
Order with no problem gets 100 points per order
Minor problem gets 0 point
Moderate problem minus 100 points
Severe problem minus 500 points
Grade are evaluate by:
<84.5 Fair
<96.5 Good
<98.5 Very good
Above is Excellent
Total points / total orders = final grade (or percentage)
As of now i got : 53800 (points) / 624 (orders) = 86.22(%)
Now that I know what grade I've got so far and I also want to know how many more perfect orders I need to achieve my next targets or in case some bad orders come by and how far they pull me down.
Attached is my work sheet so far and I'm just missing the calculation for the orange shaded part.
Amazon seller rating calculator.xlsx
I have a list of data in columns Q R S and i need to in between each line insert the data from columns D E F underneath and then underneath that the data from G H I
so bascially i have a list at the moment like this
Q
R
S
[Code]....
There is 2 sheets in total , 1 i need to inc 1-1 draw as above and the other sheet to be exc the 1-1 draw . I have attached an example of what i am trying to achieve . can offer as i have had this on excelforum.com with over 150 views and no solution
I want to separate the texts and numbers in a column A1.Please find the attachment.
sampleworkbook.xlsx
creating a formula to separate the text from the numbers into 2 separate columns.
Examples are:
A1= Angel Romero 260.00
A2= Wieben Chiropractic Clinic 74.00
A3= R Ricardo Ramirez Dds 340.00
The 'Text to Column' function does not work because there is no fixed width and no deliminater. To add in a deliminater, like a "", is an option but there are thousands of cells to do this to.
As you can see, using LEFT, RIGHT and MID functions become tricky since the deliminater would be a "space" but there are often several "spaces" in the string of characters.
Is there a way to SEARCH or FIND the first number and let that be the deliminater?
I have cell with lot of texts, punctuations and numbers all mixed together,
example :
private-4089 AND road ESCORT,trailer-4111 & test vehicle
I need to remove all texts and keep the numbers only: 4089, 4111
then I hope I can do text to column to put each number in a cell
I have an address with numbers. Sometimes has only address number and sometimes the appartment also. I want to separate those numbers in two different columns.
Like the attachment : SAMPLE.xlsx