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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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 Replies!
View Related
Split Single Text Cell Into Multiple Rows, Using A Comma Delimiter
I have a string of text in one cell on Sheet 1 (ie., A1, Sheet 1), here is a excerpt: A-dec International Inc., A. Bellotti, A. DEPPELER S.A., etc ... What I need to do is split the cell into separate rows, using the comma as a delimiter. I will be reading the cell from another sheet and need a formula that will provide me with A1: A-dec International Inc. A2: A. Bellotti A3: A. DEPPELER S.A.
View Replies!
View Related
Split String At First Delimiter Only
I have a column with cells that look like: XYZ - JobABC - Area 1-A. I'd like to split the column into to and have it look like: XYZ <next column> Job ABC - Area 1-A Normally I'd use the text to columns function and "-" as delimiter, however, there are other instances of "-" which I do not want separated. Note that the XYZ can vary in length and therefore fixed width will not work either. I have attached a small sample with the different variations that I could encounter.
View Replies!
View Related
Split Cells Into 2. Space As Delimiter
I recently got this task by my work employer to transfer information from several non-organized Excel-documents into one, complete and organized one. The thing I have to organize right now is a column with thousands of zipcodes and zones in one single cell, only separated by spaces, like this: Zipcode Zone 115 54 Gothenburg 443 67 Skane What I want to do is instead of having to go through 24h of braindead control/paste is to use some easy and fast function or script that could pull out the last piece of string of information in every cell in that column into its own separate cell. Although I am using Open Office Calc/Gnumeric on Ubuntu Dapper, if a script is necessary and only work with Excel on Windows/Mac, that is no big problem for me to solve. So please, any suggestion is welcome. I will attatch two examples for you to look at, to get the whole picture.
View Replies!
View Related
Keywords As Delimiter To Split Cells
I've got a column of data containing addresses. What I want to do is split the column after certain keywords such as "street", "road", "close" etc and move any words that come after these in the cell into the next cell to the right. For example, in one cell I might have "54 Burberry Road Finsbury London"... what I want to do is have part of the text remain in the original cell i.e. "54 Burberry Road" and the rest, "Finsbury London", moved to the cell on the right. Not all cells in the column will match this format so if it doesn't find a keyword, nothing will occur and it will move to the next cell down etc. until it reaches the end of the column. There are no delimiters (apart from the spaces) between the words. I haven't figured out all of the keywords that I will need to search for yet but I should be able to extrapolate from something fairly generic.
View Replies!
View Related
Split Column On 2nd Occurrence Of Delimiter
I have one column in a worksheet that I want to split into 2 columns. If there is one "(" in the cell, I want to split at that point, but if there are two "(" 's, I want to split at the second occurrence. I can use the Like operator to identify the cells, but is there a way to specify which delimiter in a sequence of identical delimiters to split at? Here are templates (not actual data) of the two type of cells: Name1 (ABC) (Name2, Name3) Name4 (Name5, Name6) For the first, I want to split on the second "(", before Name2, and for the second, I want to split on the first "(", before Name5.
View Replies!
View Related
Move Delimiter Data To New Rows
I ran across this macro in the posting " Move Adjacent Cell Into Rows, Same Column" and it is exactly what I need but when I try to use it I get a compile error on the Range("i1").Resize(n, UBound(b, 2)).Value = b. and several other problems. I also change "b" to "c" because this is where my data that I need to work with is located. I have a similiar case much like the posting statement above where I have a spreadsheet on each row in cell "C" a EP CODE that have various information in it. My problem is that in the EP CODE column, it contains several EP CODE separated by semicolon (";") delimited . In order to serve my needs, each EP CODE instance needs to become a new row (record) whereas all of the information (except for the EP CODE) is the same and need to be repeated for each new row. Example: Before Name Location EP code WillisFremont 1;2;3;4;5;6 RaysSan Fran 7 SuNew York 8;9;10;11 After namelocation EP code WillisFremont 1 WillisFremont 2 WillisFremont 3......................
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, I’m not sure how to implement it into my situation.
View Replies!
View Related
Extract Each Word In Cell With Delimiter
I am trying to extract the first letter of each word in a string. The string can contain either a space or a "/" as a separator. e.g. blue green or blue/green. If there is no space or "/" in the string, I need to return the 1st 2 characters. I'm using the following formula but it is returning #VALUE! =IF(ISBLANK(T2),"",IF( FIND(CHAR(32),T2), LOWER(LEFT(T2,1)&MID(T2,FIND(CHAR(32),T2&" ")+1,1)),IF(FIND(CHAR(47),T2),LOWER(LEFT(T2,1)&MID(T2,FIND(CHAR(47),T2&" ")+1,1)),LEFT(T2,2))))
View Replies!
View Related
Replacing Carriage Returns With Delimiter Within A Cell
I need some direction on how to proceed with dismantling an address cell. I have a workbook that one field is an address label field. What I mean is the entire address is entered in one field just as it would appear on an envelope. I need some suggestions on how to break this apart and create individual fields for the individual parts.
View Replies!
View Related
Transpose Column Range Into Single Cell With Delimiter
I want to add a Punctation mark (comma), like this: , and also want to add punctation mark (colon), like this: : In this moment I have below macro: Public Sub CombineCells Dim Combined As String Combined = "" For Each Cell In Selection Combined = Combined & Cell.Value & ":" Next Cell Selection.Cells(1, 4).Value = Combined End Sub the effect shoud be like this: before: --A 1-C 2-D 3-E 4-F Etc. after transposed: --D 1-C:D,E:F Etc.
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
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
Copy Row And Split Values Within Cells
i am trying to copy a row a certain number of times based on the number of values within a cell. (E4:E--) and then split the values contained in that cell into unique rows. i have the following: A B C D E F 3/20/07ARROW INC 2065114 $10,555.24 606 4/6/07 3/18/07NORTON 2017304 $13,206.15 715 4/6/07 3/22/07HARKMAN 2025685$8,127.04 167 226 452 4/6/07 3/19/07AMEREX INC 2019933$14,131.64 186 189 4/6/07 3/23/07G SQUARED 2029062$8,587.68 204 233 4/6/07 what i need is: A B C D E F 3/20/07ARROW INC 2065114 $10,555.24 606 4/6/07 3/18/07NORTON 2017304 $13,206.15 715 4/6/07 3/22/07HARKMAN 2025685$8,127.04 167 4/6/07 3/22/07HARKMAN 2025685$8,127.04 226 4/6/07 3/22/07HARKMAN 2025685$8,127.04 452 4/6/07 3/19/07AMEREX INC 2019933$14,131.64 186 4/6/07 3/19/07AMEREX INC 2019933$14,131.64 189 4/6/07 3/23/07G SQUARED 2029062$8,587.68 204 4/6/07 3/23/07G SQUARED 2029062$8,587.68 233 4/6/07 the number of rows will vary and obviously, the number of rows will increase as the rows are copied X times to correspond to the number of values contained within cell E. it is not showing correctly, but cell A is date, cell B is company name, cell C is PO number, cell D is price, cell E is Department numbers and cell F is end date.
View Replies!
View Related
If Cell="x" Then Move Row To Another Worksheet
I have a spreadsheet that contains rows that my sales team complete for their pipeline. I have a drop down at the start of the row (column a) that has the status of the sale. If they put the status to "Lost" I'd like the entire row to be cut and moved to another worksheet (lost) so that I can do additional analysis at a later date of all lost sales.
View Replies!
View Related
Split Data In A Cell And Get The Value For The Split Data From A Different Table
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.
View Replies!
View Related
Select Random Row, Split Cells To Different Worksheets
I am trying to write a macro to: 1. Randomly selects 50 rows from 834 on "worksheet 1". There are only two cells per row. 2. Then, it clears "worksheet 2" and "worksheet 3". 3. From each selected row in "Worksheet 1", it splits the two cells and copies the results into two different worksheets, one column per sheet. 4. The rows in the two new worksheets should be congruent (i.e. same selected order from worksheet 1). Keeping the order the same is important. This is what I have so far: -------------------- Sub Macro1() Dim rng As Range Dim iRow As Long With Rows("1:834") Do iRow = Fix(Rnd() * 834 + 1) If rng Is Nothing Then Set rng = .Rows(iRow) Else Set rng = Union(rng, .Rows(iRow)) End If Loop Until rng.Areas.Count >= 50 End With rng.Select Selection.Copy Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) End Sub -------------------
View Replies!
View Related
Move Row Up Or Down
I have a workbook with several sheets each containing different numbers of rows. These rows contain data from clients (codes etc applying to motors). The rows have been randomly populated and I am having to move rows from somewhere in the sheet up or down so the codes are grouped according to which motor they apply to. What I want to do is select a row and be able to run a macro that will move it where I want. The destination can be up or down and any number of rows away. Does anyone have any ideas how to do this? I was thinking that if I selected the row I wanted to move and the one I wanted to move it to then run the macro it could be moved. Its not to replace the destination row, but be inserted alongside it.
View Replies!
View Related
VBA To Move A Row Up When Value Is Not Zero
I have 8 rows (B345:B352) that have a formula similar to this:=IF(Site1!B53>0,CONCATENATE(Site1!$B$53," Scansys 942 @ ","$",Site1!D53,".00"),""). By the way, can the formula be shortened? I tried formatting the merged cell but nothing changes. When Site1!B53=0, then there's nothing in the cell.That's how I want it. But say, row 3, Site3!$B$53 >0 how do I programatically move (copy/paste?) row 3 to row 1 (since row 1 and row 2 are blank)? In other words, Since rows 1 & 2 is blank, I don't want the two empty rows there, so I would like to move row 3 up to row1.
View Replies!
View Related
How To Move To The Next Row In A Do Loop
In a macro I made, I have a do...while loop: Do While Not objXLworkBook.ActiveSheet.Cells(nSequence, 1).Value = gcstrEMPTYSTRING .....bunch of code Loop In my file, the second row of data always contains blanks (i.e., I've got a HDR record, blank row and then rows of data). How do I make my code bypass that empty row of data and then continue processing all other records? Should I put a "move to next record" in my bunch of code, and if so, how do I do that?
View Replies!
View Related
Move All Used Cells On Each Row
I have rows of data starting in row 4. I have row Headers taking up columns A:G. My data, which is downloaded, can be anywhere between H4 and IV900. I need a macro that will go to G4 Then do the equivalent of arrow right to the first cell containing data Select that cell and all cells to the right of it Copy the selected range Cut and Paste it back to H4 Move to G5 and repeat all the way down the worksheet so that I end up with al of the data starting in column H rather than spread all over the worksheet.
View Replies!
View Related
Move To End Of Used Range In Row
Is there a key combination to move Right to the end of the row even though there may be blanks within that row? If I do a Shift>Ctrl>Right arrow it will highlight to the cell just before the first blank cell. I am trying to "Move" to the Right end of the table reguardless of what cell I'm in or if there are blanks in the row.
View Replies!
View Related
Move All Rows 1 Down But Skip Row 26
I've got a pretty nice Worklist setup in Excel that enables you to organize all the work you need to do in a month. However i end up with little holes in the list whenever i finish a task because what my macroes does is whenever a job is marked as done on the list it moves that job to sheet2, which contains completed jobs. I'd like to have a macro that shifts all the rows 1 down whenever a job is completed(ill just call it from the job complete macro) but it needs to jump over(skip) row 26 because that row contains some images that my macros use. Altso i would like to, if possible avoid inserting any rows or cells or hiding them because that will totally mess up my macros :p I know i know, im not dynamic enough. I've included a sample of the worksheet so you can see what i want. By the way, I've altso got some trouble with a public function.
View Replies!
View Related
First Row (for Column Titles) Not Move
I have been given an Excel file. Upon opening it, I see that the top four rows do not move even if I scroll down. What I'd prefer is that only the first row (for column titles) not move. If I can learn the name of this feature, I'm sure I can figure out how to change the file.
View Replies!
View Related
Macro To Move Row To Another Worksheet
I have a workbook with two worksheets in it. The first "TO DO" and the second "Completed". In "TO DO", I have rows of tasks starting in row 4 (row 3 is my header), going to 200. What I'd like to have happen is when I put a "C" in column C, it moves the entire row to the "Completed" worksheet, greys it out and removes it from the "TO DO" worksheet. Then if I remove the "C" from the "Completed" worksheet, it moves it back to the bottom of the list. I already have a macro to resort it based on "priority" in column E.
View Replies!
View Related
Move Row Based Off Criteria
What I am trying to do is if on the (Open Project Issues) tab column F says "Closed" it will move that row to a tab called (Closed Project Issues) tab. I was trying something like this: Sub testmove() Dim c As Long For c = ActiveSheet.UsedRange.Rows.Count To 2 Step -5 If Cells(c, 6) = "Closed" Rows(c).Cut End If Next c.......
View Replies!
View Related
Move All Data Rows Up 1 Row
I created a macro that is supposed to copy the contents of about 500 rows, and move them all up one row. One of the things that is contained in those rows are a bunch of links to Microsoft Word documents. When I try to move the 500 rows up manually (just copying, and pasting one row higher), it works fine. The link remains entact. However, when I use the macro I recorded, the link no longer works. It looks like it is there (the text used to represent the link is there), but when you click on it, nothing is there. Here's my code....
View Replies!
View Related
Move Selected Row To Another Worksheet
I have a workbook with multiple sheets. I need to create a macro and associate it with a command button that will cut the row the user has selected and paste it into the last empty row on another sheet in the same workbook, then, go back to the first sheet and delete the empty row.
View Replies!
View Related
Detect End Of Data In Row And Move To Next
if i have data across multiple rows than is it possible to analyze that data in say row 1 and once row 1 ends move to row 2. example Array Data Colmn A B C D E Row1: 3 1 4 6 8 Row2: 6 7 8 9 10 Row3: 4 5 1 2 8 Row4: 6 4 3 2 1 Row5: 2 4 1 2 2 OutPut Format Colmn F Row 1: Age is 3 Row 2: Always Same - Note this appears even though not part of data Row 3: Year is 1 Row 4: Time is 4 Row 5: Hour is 6 Row 6: Minute is 8 Row 7: ! !Not Part of Output Comment: Now row 1 data has ended. Move to Row 2! Row 8: Age is 6 Row 9: Always Same - Note this appears even though not part of data Row 10: Year is 7 Row 11: Time is 8 Row 12: Hour is 9 Row 13: Minute is 10 ! etc etc etc also note that the text that comes up in each row is not part of the data portion. Its just the number or string that gets pulled from the data portion.
View Replies!
View Related
|
TRACKER
