Split Cell Into Two And Move To Another Row And Columns
I have a report that I need to reformat where part of the information is moved from rows to columns.
The report is broken up into "sections" as follows: Each section is a series of multiple rows and is broken down as follows:
ROW 1: Contains data (in a single cell) about a Sales rep, which includes (1) rep number & (2) rep name
NEXT ROW(s): Contains data information about an invoice(s), which includes date, invoice number, client name, trans ID, etc. The invoice data can be one row up to as many as 500 rows
LAST ROW: Contains the Rep Subtotal
I need to spit out a report that contains the invoice data only (the middle part of the section). I don't want "ROW 1" or "LAST ROW" of each section in the output. For each invoice row, I need to include the rep number and the rep name for each invoice. As noted, the rep number and name is always listed in the row preceding the invoice data. The format is always a 6-digit code followed by the name. So I need to split the data into two pieces.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Split Cell By Delimiter, Move To New Row
I'm prepping this file to be pushed through mail merge. The sheet has 25,213 rows. Column I contains a long string of character (for the purposes of my project the field is called Subjects) and $ used as delimiters (approx 70 or as few as 1). This column needs to be split apart by $ and the separate Subjects that result from splitting apart by $ needs to be placed on a separate line. Once each Subject is on a separate line, all of the Subjects need to be lined up in Column I, one under another (the obvious result at this point is GREATLY increasing the number of rows in the sheet). Next, all of the data that is contained in each Subject's originating row will be copied to each of newly created rows (without copying over the Subject contained in column I). After writing this all out, I realize (once again).
View Replies!
View Related
Split Cell Data Into Two Columns
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: 9065-10A 9065-9B 9065-11C and I need to be split into 2 columns as follows: Column 1 Column2 9065-10 A 9065-9 B 9065-11 C
View Replies!
View Related
Insert Row On Sheet & Move Active Cell Row To It
I would like to create a macro that could archive entries from one sheet and insert them in another. I created one but the problem is that the entry has to be the same row each time. Example: Sheet 1 is current jobs and sheet 2 is old jobs. My macro moves an entry from Row A-5 of Sheet 1 and moves it to the top of Sheet 2. I would like to be able to scroll through each entry select it and have it moved to the top of the Old Jobs sheet.
View Replies!
View Related
Split Single Cell Into 2 Column X 3 Row Range
I'm trying to separate a cell containing: TSB w/6.5% NaCl - NS 29002TSB w/6.5% NaCl - NS 29003TSB w/6.5% NaCl - NS 29004 into a 2x3 table, in another sheet, like this: TSB w/ 6.5% NaCl NS 29002 TSB w/ 6.5% NaCl NS 29003 TSB w/ 6.5% NaCl NS 29004 TSB..... in left column and NS...... in right column. This example includes 3 items as shown above but the single cell could contain more or less than 3 items
View Replies!
View Related
Move Cell Value To Last Used Row Of Another Workbook
I have recorded a Macro but cannot figure out how to edit it to accomplish my goal beyond the first use. I want to to Highlight a Row in Worksheet A, ( Amazon Open.xls ) - Run my Macro to Cut that Row -- then Open Worksheet B (Amazon Sold.xls) and Paste that row into the next unused Row. Then copy Cell L in that row to clipboard. Heres what I have recorded -
View Replies!
View Related
Cut/Move Cell Values Down 1 Row
I got the following format of data which I need to copy the cell values to the next row if it's found. Columnl1 Row 1 00185 Row 2 Row 4 Row 5 00123 Row 6 what i want is to copy from row1 to row2 & row5 to row6. How am I going to code it in macro ?
View Replies!
View Related
Copy, Move And Delete Row, Based On Cell Value
I have a spreadsheet (Sheet 1) listing current Work Orders with each work order occuping a seperate row; Column E lists the status of the work order, with the status being chosen from a drop down list. I would like to have a macro that will copy the entire row and paste into (Sheet 2) when the status is changed to CLOSED, and clear the contents of the cells on Sheet 1. The aim of this being of course to have all open work orders on sheet 1 and all closed orders on sheet 2.
View Replies!
View Related
Move Rows To Another Worksheet Based On A Cell Value In That Row
I currently have a consolidated worksheet (thanks Bill!) called " Dashboard" that contains closed items that are marked by a validated column that can only contain "Closed, Open, or In-Progress." Is there a way to move the rows with a value of "Closed" to another worksheet called "Completed"? Also when this move is done, that row is no longer necessary in Dashboard and should be removed. So I'm guessing its a lot like a cut and paste and then a delete row/shift cells up? here are some additional information: The worksheet has a locked header that is 6 rows deep (the values for the "Status" column begins on row 7 and on.) The "status" column is at column 11.
View Replies!
View Related
Move Rows To Another Worksheet Based On A Cell Value In That Row ...
I have a excel file to keep track of tasks or actions that need to be performed. What I am looking for, is an automated utility or code that will allow excel to automatically move entire rows (so an entire task) of completed tasks to another sheet called, "Completed Actions". In Actions sheet I have a column for " status" and here you have to select from a drop down menu, either "On-going", "Urgent" or "Done". What I would like, is that once you have selected "Done", the entire row or entry, will be automatically moved to the "Completed Actions" sheet.
View Replies!
View Related
Automatically Move & Sort Row Based On Cell Value
I have a simple projectlist on sheet1 formatted like this: Column A=Projectname, B=Description, C=Owner, D=Deadline and E=Status. On sheet2 I have the same columnheaders. What I would like to do, is that if Status changes to "Done", * the entire row should be copied to a free row on sheet2, or just inserted on sheet2.row2 without overwriting anything * the now empty row on sheet1 should be deleted (so there're no empty rows) * the data on sheet2, should be sorted with D as first key and C as second. Then, if Status on sheet2 changes to "Reopened", the same procedure should happen but from sheet2 to sheet1. My first choice was to create a checkbox on each row in the Status column,but I noticed it didn't seem to follow with cut/paste even though I changed the property Locked to false. Anyone knows if it's possible to change this behaviour? I only know small bits of VBA but my idea is this: ...
View Replies!
View Related
Move Rows To Worksheets Based On Cell Text In Row
I download our monthly bank transactions (.csv). I have a workbook with sheets named for each creditor or expense. I would like move each row, based on the specific word in a cell row, to the specific worksheet. Here are some typical transactions in the Description column: Some are specific: 1/7/2008ACH WEB-SINGLE 9085863 VONAGE AMERICA VONAGE Moved to the Vonage worksheet. Others are not so obvious: 2/1/2008CHECK CARD PURCHASE XXXXX4636 BOSTON MARKET #0450 GREENSBURG PA Moved to the Eat Out worksheet. 2/21/2008CHECK CARD PURCHASE XXXXX4636 ASPCAPS XXXXX0028 MD Moved to the ASPCAPS worksheet. 2/19/2007POS PURCHASE POS54309901 0014264 PITTSBURGH ST GREENSBURG PA Moved to the Grocery worksheet. I was able to find the following while during a Search: Move Cells Containing Specific Word In Column To New Sheet The question was answered, in addition to Dave Hawley, by Bill Rockenbach who inserted the following code - Sub FindWord() Option Explicit Sub FindWord() Dim Sentences Dim Word As String Dim i As Long Dim iWordPos As Integer Dim lRow As Long Dim sWord As String If this is a possibility for what I'm looking for, Im not sure how to implement it into my situation.
View Replies!
View Related
Move Data From 2 Columns Into Multiple Columns At Each Change
I am trying to write a macro that will take data from column A and use it as a column header in column C, and then take the corresponding data in column B and list it under the column header in column C. When the value in column A changes it will move on to column D and then list the corresponding data from column B underneath it in column D until all of the data in the 2 columns is spread across the worksheet. Example:..............
View Replies!
View Related
Sort Multiple Columns, With Blanks, And Move All Columns Into One
I found this code on Ozgrid to sort all columns of a worksheet that were continuous with no gaps or spaces that works well: Sub CopyToA() Do While ActiveCell <> "" Range(ActiveCell, ActiveCell.End(xlDown)).Cut Destination:=Range("a65535").End(xlUp).Offset(1, 0) ActiveCell.Offset(0, 1).Select Loop End Sub However, I've tried to manipulate the code myself to 1) find all columns that aren't empty then 2) sort each column individually (WITHOUT expanding the sort to other columns) and 3) combining all the numbers into one seperate column. There are many posts concerning sorting but not one that addressed this particular situation.
View Replies!
View Related
Split To Columns
i have in col A using 2007 these examples . 10. * 7x7 Showmed'cash (7) 3g b 5. * 08x90. Bean Rock (10) 6g b 8. * 31 Thenaia (12) 3f b 11. * Stormy Masika (9) 3g b I have managed using text to cols separateting info inside the brackets and to the right . Also the numbers very left . My problem is there arnt any more things to use in text to cols to separate examples 7x7 Showmed'cash , 08x90. Bean Rock , etc .
View Replies!
View Related
Split Address Into Two Columns
I'm trying to split the address stored in column A into two columns (i.e. columns B & C). I got more than 30,000 addresses stored in column A and got the following excel function to do the job. However, it seems not to exactly solve my problem. in column B : =LEFT(A1,FIND(" ",A1,20)-1) in column C : =right(A1,len(A1)-FIND(" ",A1,21)) First, using the above functions, I got to manually drag the formula to the end of column containing 30,000 records! I tried to use vba to perform the job the script failed to do so. I know there has been something I missed in my vba script but do not know how to correct it. for k = 1 to 30,000 Range("b" & k).value = LEFT(range("A" & k),FIND(" ",range("A" & k,20)-1)) Range("c" & k).value = RIGHT(range("A" & k),len(range("A" & k)-FIND(" ",range("A" & k,21)) next What I really want to do is split the addresses in columns B & C with the first text ended with "Street" or "Road" stored in Column A and the other text after "Street" or "Road" in column C. column A : 128 Johnway Road, 12/F, Flat C, Kowloon, Hong Kong column B : 128 Johnway Road Column C : 12/F, Flat C, Kowloon, Hong Kong
View Replies!
View Related
Split Text Across Columns ..
Need to split the WORD into Col B and put the DEFINITION into Col C. Here's an example of what's sitting in A1: Title A description of record contents I've tried using the text to columns but can't get it to work. (since the Words are all different lengths, something gets chopped off) What I have today: In column A (within a single cell is both the Word & it's Definition). I need to extract the word ONLY into a new column (B) and extract all the other words into column (C) (without the dash)... I've also tried : =LEFT(A1, FIND(" ",A1)-1) and successfully stripped the Word into column B but can't find any functions to extract the rest properly into C I JUST noticed, some WORDS are multuple...example: Information Protection Level Used to identify information protection values per Pro 2227 Can you provide a function for doing a 3 word extraction to Col B? I guess, what I REALLY need is for it to take "everything up to the dash" and put in column B......then put everything after the dash and put in col C.
View Replies!
View Related
Split Description Into 3 Columns
I need help to split text (shown below) into 3 columns, now all text is in one cell. e.g., HIPP 1 PLUS PROBIOTIK 600 G MILCHNAHRUNG PULVER 1607000286 I have over 30,000 rows of data which contains different number of spaces within a cell. Please see attached file.
View Replies!
View Related
Split A Column Into Multiple Columns
The spreadsheet contains over 21,000 rows of data, and one of the columns (D I think) contains data as in the two examples below. What she wants is to split this column at the semi-colons ( and have the column header as the "field" name. Unfortunately not all the cells have the same number of "fields" as you can see. Some don't have an "addressLineTwo" while others also have "stateprovince". Is it possible to split the column so each "field" goes into it's own column? Please note that if a "field" is missing there is not two semi-colons to indicate an empty field. I'm also fairly certain that, between them the two examples below show all possible fields. Data Examples. addressLineOne:Road Belen Staana;addressLineTwo:Costado Oeste;city:SAN ANTONIO DE BELEN;highRate:194;latitude:9.97631;longitude:-84.20038;postal4005 addressLineOne:1766 Homestead Drive;airportCode:ROA;city:HOT SPRINGS;highRate:500;latitude:37.99662;longitude:-79.83079;postal24445;Rating:52;stateprovince:US Didn't there used to be a "Split" function that split text over two cells? I'm sure I used it years ago, but can't find any mention of it in Excel 2003.
View Replies!
View Related
Split Text Among Columns By Using Functions
Once again I return to the brilliant ones on this board. I read the Excel help page for "Split text among columns by using functions". But my parsing task is more advanced than what I could gather from this function. Here is the contents of cell A1 to be parsed: Pack type,(make selection),Pack A[=4.95],Pack B[=5.95],Pack C[=7.95] I need to extract 4.95 into cell A2, 5.95 into A3 and 7.95 into A4. How Oh also, I have many variations of that example, and want your solution to work for the variations. So here is another actual cell that I have to be parsed: Qty. discount,(Make Selection),1[=18.95],2 to 4[=17.95],5+[=16.95] So, each extracted value will always be preceded by = and followed by ]
View Replies!
View Related
Split Text To Columns Over 2 Sheets
I have data in Column A that is from a text file that contains 50,000 rows , this is pipe delimited data that is 300 columns wide. I would like to be able to keep the columns but using the Text to Column functions means that I lose some them. I was wondering if anyone knew how to separate the text in to 2 sheets, with the first 200 columns in Sheet 1 and the remaining in Sheet 2. The reason I would like to separate the information in to cells is so that I can investigate the data better. Its not possible to traspose this information as the rows are nearly 50,000 long. Here is a small sample of what 1 row is like, it not the entire row. Each "|" character represents a break and a new column. PHP A|01234567/163|01234567/163|AB123456B|Mr|Gordon|Brown|01-01-1960|1|10 Downing Street|SW1A 1AP|4|||||||||||||||||||||0|0|16-06-2009|16-06-2009||
View Replies!
View Related
Split Data In One Column Into Two Columns
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. Column A Aa3 |AAA A3 |AA- A2 |A A3 |BBB+ Aa3 |AA Aa2 |AA+ Desired Results Column B
.Column C Aa3
AAA A3
..AA- A2
..
A A3
..BBB+ Aa3
AA Aa2
AA+
View Replies!
View Related
Split Text Across Columns Formula
I got a question regarding a what formula can i use to act like the Split method in programming. I would like if this can be done with a formula not programming cos i am a programmer Basically i got a column full of data in the following format:.dddd.ddddddd.ddddd. Now the number of the "d" can be random in between the dots. So i would like to be able to split the line by looking at the "." .There is one thing though that each line does have 3 or 4 "." characters.
View Replies!
View Related
Split Text Across Columns With Delimiters
I have a column full of text with most data separated by commas, except sometimes between the commas there is a string, marked by ' ' , which itself contains commas. For example: 45,'im a string, look at me',67,43,5,'im another string, look at me',78 I try to make excel put all the data into columns, so 'im a string, look at me' will have its own column, instead of being split into two columns. I tried telling excel that the ' character marks strings, but it just removed the apostrophes and kept splitting any string that contained a comma. I tried to use a special delimiter,' but excel didn't allow me to. Does someone have a macro that will do the text to columns for me, or is this possible to do with the regular text to columns feature of excel?
View Replies!
View Related
Split Address Field Into Separate Columns
I have a spreadsheet with a few thousand rows, yeah i know..lol Each one of the rows has contact details for individuals. I have the address for each person in one field with up to four different sections, seperated by tabs. How do I go about seperating each part of the address so it is in a different column? I have tried text to columns, and it only seperates the first portion of the address, seperated by the delimiter 'tab'.
View Replies!
View Related
Split Data Into 2 Cells, Text To Columns
i have cells with city and state in them and i'm trying to separate the one column into two columns. the problem is, i'm trying to keep the city name in one column and the state in the other. some of my cells have two word cities like new albany, or upper arlington. the text to columns feature is separating those cells into 3 columns not 2. is there a way to do this? example: worthington, oh upper arlington, oh text to columns splitting upper arlington into 3 different cells because the only delimiter in the cell is a space. i need to keep upper arlington in one cell and oh in another.
View Replies!
View Related
Split The Individual Dimensions Into Seperate Columns
I have a lot of carton dimensions that are always presented in the same manner and would like to be able to split the individual dimensions into seperate columns. The format is always: Length Width Height. Presentation of data is always ???x???x??? or ??x??x??? (ie two or three chrs seperated by the letter X). If the full string is stored in column A I would like B to display Length, C the Width and D the Height. I find it easy to use LEFT for Length but struggling with Width and Height which I'm sure are easy, just can't get my head around it.
View Replies!
View Related
Split Multi-Lined Cells Into Columns
So I have a worksheet filled with a lot of text in individual cells in one column. What I would like to do is split up the text into different columns in one row so that I can use it easier. The data would be seperated by blank lines within the cell (basically when you hit alt+Enter in the cell). Sample data of a cell is below... Cell A1: _____________________________ Directions: Baking an Apple Pie First you must .... etc Total time required: 30 minutes _____________________________ What I would love to be able to do is split up the above sample cell into three different columns, in the same row. So the above cell would become... Cell: A1 Cell: B1 Cell: C1 ____________________________________________________________________________ Directions: Baking an Apple Pie | First you must .... etc | Total time required: 30 minutes ____________________________________________________________________________ And then so on and so forth for the rest of the data in that column. I realize that VBA code will be needed for the above... I searched the forum for something that would address this particular issue but have had no luck.
View Replies!
View Related
Split Single Column Into Multiple Columns And Rows
I have 300 rows worth of data that looks similar to this, all organized in one column: John Q. Smith Programmer 2111 NW 13th St Anywhereville, USA, 55555 (555) 555-5555 Joe P. Snider Organizer 5645 NW 45th St Anywhereville, USA, 55555 (555) 555-5555 Patty Williams Accountant 6454 NW 34th St Anywhereville, USA, 55555 (555) 555-5555 As you can see it is consistent with the name, position, address line 1, address line 2, Phone number, for every single entry. All my names are already alphabetized so I don't have to worry about it. What I want to do is have the information for each entry translated into 5 separate columns so it looks like this. John Q. Smith Programmer 2111 NW 13th St Anywhereville, USA, 55555 (555) 555-5555 Joe P. Small Organizer 5645 NW 45th St Anywhereville, USA, 55555 (555) 555-5555 Patty Williams Accountant 6454 NW 34th St Anywhereville, USA, 55555 (555) 555-5555 Supposing I started the sheet in the top left corner at A1, I was just going to have cell B1=A1 then Cell B2=A6 then just autofill down column B but it doesn't work. I have seen some people do something similar to what I want with VBA but I am convinced there has to be something simple in with an excel formula seeing as my information is already so organized and consistent.
View Replies!
View Related
Auto Change Row/font Colour And Move Row
Attached example sheet. Basic table of data, with column F being a validation list, is it possible that when choosing COMPLETE from the list, the row and font change colour, and then move to the top of the list? I'm not the only simpleton using the sheet so I need it to be as simple as possible. I know it doesnt seem like a big thing but the sheet we are working from is huge and I dont want people cutting and pasting away, I just dont trust them with my spreadsheets.
View Replies!
View Related
Split Text Across Columns (apartment Number & Address)
I have a 2000 row sheet with an address field that contains the apartment number and the address. The text is displayed as unit number, then hyphen and the building/street number for example '101-1234 15th Street'. I'm looking to cut the building number from the cell and paste it into a seperate column. Ideally removing the hyphen completely, so that I have two columns 'unit number' and 'building/street address'.
View Replies!
View Related
Cell Content > 255 Then Move "excess" To Row Below
I'm looking to combine a number of worksheets into a summary sheet .. but on some the narrative is more than the 255 characters. How can I , where this is the case, insert a row below.. say input "Continued" in column A and then copy the contents of the cell which is more than 255 to the cell immediately below it.. and maybe do this again where users input more than 510 characters. Alternatively, is there a way of getting around the copy and paste values.. where it only inputs 255 ?
View Replies!
View Related
Import A Text File From A Specific Folder Into A Sheet Without Split It To Columns
I have a variable list. Each column will be 250 digits and numbers of rows will be variable. I want a macro to import a text file from a specific folder into a sheet without split it to columns. So we will work only in column A Then macro will find B1002 wording in A1. if it is exist, it will copy the next 36 digits after B1002 wording if not then it will search A2 row. The next step will be to search and find another wording "B1001" if it finds it will replace copied 36 digits text. If it can not find B1001 wording it will go to upper row and search B1001 wording here and paste the text. This will go on till row shows #END. This means it reached the end of the list. And then macro will save this file as text file to another folder. So macro will go to beginning to open other file in the folder and this will go on till last file in the folder. This is the logic of the macro. Here is the sample of what I want. Original Data: ....
View Replies!
View Related
Import CSV Into Worksheet Split Into Columns Then Copy Rows By Criteria
I have requirement to extact data into a spreadsheet. This data is extracted from CSV file which is huge normally over 7MB. I have found a macro on the internet which I have included with this post. Sub split() Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double 'Ask User for File's Name FileName = InputBox("Please enter the Text File's name, e.g. test.txt") 'Check for no entry If FileName = "" Then End 'Get Next Available File Handle Number FileNum = FreeFile() 'Open Text File For Input Open FileName For Input As #FileNum ................. I have also included reult data that I get after running the macro. I have cut down on data due to attachement restrictions. But usually there are multiple spreadsheets named "Sheet1, Sheet2, Sheet3 and so on". I have numerous requirements. First of all I want to be able to spreate data into columns. For now how I do that is by going to data -> text to columns and then selcting delmited and then selecting comma as my delimiter. Second thing I want to do is is only extract range of data from this output into a new worksheet. I am only interest in the name like for example ALBANY-Serial0/0/0 and data that is in the range of 8:00 AM to 6:00PM. So the new sheet should only have name and for that name data in the range of 8:00AM to 6:00PM for all the sheets "sheet1, sheet2 etc" until all data has been extracted.
View Replies!
View Related
|
TRACKER
