I am trying to find away to split up data that is entered as text into a cell,and split that text data into 3 columns. Sample text enters in a cell can be:
10-3 or 1-3-1 or 12-11-0 or 1-6 or 8-8.
As you can see, the text entry can have two or three numbers in it. I would like to split this data so it looks like this: 10-3 would be cell A5=10, cell A6=3 and cell A7 is blank.
Another example would be 1-3-1 would be split A5=1, A6=3 and A7=1.
I know about the text to column function, but that is not the case as I want the data to split automatically with a formula.
Here's my problem. I have a cell where there are many data strings seperated by ",". Each data string has a seperate value of its own like for e.g: A2: aa,ab,ac
String Value aa 1 ab 1 ac 3
What I want it accomplish is that, split the A2 cell into the different data string entities seperated by ",", then get the corresponding value of each of the data string entity, and to take the average of all the values of the different data string entities.
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
I'm using Excel 2000 and I have a spreadsheet with 4 columns (A-D) and many (500+) rows.
Part 1: ######################################### Colums A & B both contain identical data - a first name and a last name in the format "John Doe".
I want the second word ("Doe") removed from all cells in Column A so that only the first name remains, and I want the first word ("John") to be removed from every cell in Column B so that only the last name remains.
So, where A1 & B1 both started with the data "John Doe" now A1 contains only "John" and B1 contains only "Doe". #########################################
Part 2: #################################################### Column C contains addresses in the format: "#5 - 123 Fake Street, Some City, CA 90210"
There is ALWAYS a comma and a space after the street address, then the name of the city or town followed by more data which may include one or more commas.
I would like everything BEFORE the first comma to remain in column C, and everything AFTER the first comma & space to be moved into Column D of the same row. The first comma and space are not needed again.
So, where C1 started with "#5 - 123 Fake Street, Some City, CA 90210", it now only contains "#5 - 123 Fake Street" and D1 now contains "Some City, CA 90210". ####################################################
I have a set of data that has cost associated with it that is broken up into several categories repeatedly. The subcategories are NOT defined to a specific set of subcategories i.e. they can be anything. The subcategories may also be alphanumeric and have spaces/other special characters in them. What I want to do is to be able to pick the cost and slot it in the appropriate category in the main sheet.
The exact number of rows and columns in the data is not fixed so it would be great if this is editable in the VBA code depending on how many rows and columns the final data set has.
An example is attached. In the example I have put all the cost as 1 for simplicity but in the actual document all the costs are different values. Also in the example I did not put any category as alphanumeric or with spaces/special characters but in the actual data there may be.
The logic I can think of is to first identify all unique values in the array, insert them as new rows, then add up the cost values (this is just a suggestion).
I am working on a project and would like to see if there is a solution for it. i have a workbook that has data entry that is summarized at a master level but I need to automatically use a formula to update another sheet after clicking an udpate button. The data from the data entry sheet needs to be allocated to all the lines that has the same master item based on the formula. A test workbook is attached ...
Now that the calculations are working, with the press of a button, I need to be able to select a range of dates and copy all lines within the range to a seperate sheet with the desired name under the same headings they currently reside under. I have included some modified code that is being used in another spreadsheet that was created for me, but I do not pretent to understand all of it and I no longer work with the creator of the spreadsheet. How do I use a button to open the form for date selections and entering the name of the new sheet, and then use the start button on the form to begin the matching and copying to a new sheet? If there is an easier way I am all for that too.
I'm working on a datasheet that needs some tweaks...
In column A, I have these data CA 902-20 GA 100-10 UP 200-20 WC 100-20 UP10-299 Best99-000 City #123-89
how can I tidy the data into column B so that they have it will have a standard format of a single letter followed by (no space) 5 digits. ex: C90220 G10010 U20020 W10020 U10299 B99000 C12389
I am thinking using Left and Right formula, does that sound right?
My data within a cell has 3 values separated by commas i.e. abc, def, ghi. I want to separate these values into their own cells. Is there a formula or command that can do this? of course I can go into each cell and cut and paste the values onto another cell, but I have thousands of cells that need to be reformatted.
I want the script to find if the value entered in the form is matching the values in column 'A' in the database and if it matches then it needs to select the cell as active cell - to populate the form details. And if there is no matching value found, the script needs to select the last empty cell of the column 'A' to populate the data entered in the form.
1) Form has 10 different fields that needs to be filled by the user.
2) Field 1 - is a text box for 'Request #' to be entered by the user.
3) After filling in all the fields - once clicked on OK, the form should search for the the 'request #' entered on the form in the database (Form and the database are in the same workbook).
4) If the 'Reqeust #' in the Column 'A' matches the the 'Request #' entered in the form, then the matching cell should be selected (Activecell -Were the data can be overwritten, with the new entry)
5)If there is no matching 'Request #' found in the database, the script should loop to select the next available blank cell in column 'A'. So that the form data can be entered.
I have a list of first names and surnames, the first name and surname are in one cell together but seperated by a commer.
For example in A1 would be Steve, Jones
What I want to do is have this split into two new cells. So in A2 I would have his first name and in A3 his surname. Formula to do this is what I cant get to work.
For each cell in column A, I am trying to split the data between two new cells. The data in each cell is separated by a “|”. All information to the left of “|” should go in column B. All information to the right of “|” should go in column C.
I am trying to split data from a cell into two columns. I have tried the LEFT and RIGHT functions but as the data held is not always the same number of characters this does not work for the LEFT function. The only constant thing is that the last character will always be in the second column. An example of data would be:
I am looking for two formulas (col. B+C) in order to split every second value from Col. A into two adjacent columns. Empty cells, in col. A, should be ignored. The requested result is shown in green and red.
Ihave data in column "B" like bellow & i want split that one cell data to different cells base on space. i mean after each space data should add to next cell ( from column "C" data can split). (formula or macro)
I would like to separate data into multiple workbooks based on a unique value in the column. For instance if I have Departments in Column F, that has data of accounting, HR, etc. I would like to put all accounting data into a accounting workbook and all HR data in a HR workbook.
every month I import (from SAP) Local Authority staffing data for 200 schools into an Excel template. After a bit of tweaking I end up with a single spreadsheet showing formatted and adjusted data for all schools, with relevant headings separated by page breaks.
This is fine if for distributing a hard copy to schools, but I want to email it. I've seen macros that would allow data to be split onto separate tabs, but is there a way to break it down into 200 separate spreadsheets? I confess to not knowing any VBA, so be gentle with me
I've got an Excel document that is generated from a third party programme (See ExcelDoc attached)
I'm trying to manipulate it so that a macro creates a CSV file like the one attached (see THK.csv)
I need to have the following columns in the CSV file populated using the Excel spreadsheet
Column A: Will need to have the NUMBER that is in cell B2 of the Excel file in this case the number is 0714 (need to keep the preceeding 0 too)
Column B: Will need the phone number Column C: Will need the fax number Column D: Will need to have the email address Column E: Will need to have the date only Column F: Will need to have the Order Number Column G: Will need to have the Customer name Column H: Will need to have the Customer telephone number Column I: Will need to have the Customer alternative telephone number **Column J: Will need to have the 1st line of the address (after First Address in xls doc)
I've managed to get a macro that will take the information and transpose it into a new document and rename it as an CSV file but I am having a lot of problems trying to get the macro to create the CSV file in the format that I need (see following macro)
I've attached the xls document (the one that is generated and the CSV file which is the format I'm trying to achieve.)
**This cell is generated and will have commas at the end of each line of the address and will be terminated by a period (at line 4 of the address. Sometimes there will be 4 lines to an address but sometimes there may only be 2 or three. In the case where there is no information I need to have a blank ( null) value in the cell
I am having a sheet where I keep track of when online surveys have been sent to users. The users enail address (column K) may be on the list for several times, but I need to make sure that there is at least a 7 days pause between sending the first mail and the second, depending on the visit date (column G).If there are less than 7 days between two entries with the same email address, the user is not qualified for taking another survey.
I was thinking to write an IF formula which returns either 1 or 0 and then let conditional formatting highlight and HIDE the row via a VBA loop.
Part I... I got the answer 1987, and now I want Excel to take the numbers out and display... 1 in one box then i set + in the next, then 9 in the 3ed. box, next box +, then 8, then +, then 7 in the last so that i can have Excel make a SUM of it all to 25.
How do I split 1987 and put the numbers in different boxes?
Part II... I want to make A=1 B=2... all the way up to 9, then start over again with J=1 K=2... up to 9 again and then over again.
So that if I write my name it comes out as a value of 14 (Odd = O=6 D=4 D=4 =14)
(AJSØ=1 BKTÅ=2 CLU=3 DMV=4 ENW=5 FOX=6 GPY=7 HQZ=8 IRÆ=9, It's the Norwegian alphabet, that's why there are some extra letters)
So how do I set up my Excel so that is ANY name is typed in I can get it out into a number from the values assign?
Is there a formula that will allow me to look for the existence of any number value in a row of one worksheet and then return a specific number value in a cell on another worksheet? For example, if the formula finds any number value it will always return the number 15 to a cell on another worksheet.
The task involves summarising about 10,000 lines of data into months by Plant by Process. I can achieve it by stepping through a couple of schedules but I'm sure it can be done using one formula (but I can't find it!).
