Split Cell Contents To New Rows?
Jul 25, 2013
I'm trying to split the contents of a cell to a new row (with data from other columns on the row copying across) below the originating cells row. Most of the split cell macro posts I've seen split the contents to a new row at the bottom of a range, however I'm looking to split the contents to a new row directly beneath the original row where data will already exist. I don't want to copy over this data I just want to push the data down depending on the number of rows created.
The contents in the cell being split can vary (1:n), so the number of rows needed to be inserted depends on the count. The majority of the contents in the cell is separated by a line feed (Alt Enter) but some may be separated by a tab or comma.
Essentially what I'm trying to achieve is something from this
A
B
C
D
[Code]....
View 4 Replies
ADVERTISEMENT
Jun 6, 2014
I have data in Col A. and i want to split (50 characters) in each row.
I know the formula but dragging formula to following rows is not working.
I have to write the formula manually. I write in b1 B2 and B3 as follows.
I want following
=MID(A1,1,50)
=MID(B1,251,50)
=MID(C1,501,50)
When i drag down the formula to bottom rows it repeat
Desire Formula in COL B
aaa asdf 242424 alkjsaf 32234 asfasf 23234 dfdfdfd
=MID(A1,1,50)
aaa asdf 242424 alkjsaf 32234 asfasf 23234 dfdfdfd
=MID(B2,251,50)
[code]....
I want to copy down the formula to 10k rows. When i select b2 and b3 and drag down i get following results.
aaa asdf 242424 alkjsaf 32234 asfasf 23234 dfdfdfd
=MID(A1,1,50)
aaa asdf 242424 alkjsaf 32234 asfasf 23234 dfdfdfd
=MID(B2,251,50)
aaa asdf 242424 alkjsaf 32234 asfasf 23234 dfdfdfd
[code]....
View 4 Replies
View Related
May 2, 2014
If Cell A1 Contained 1,154,546,487,47,36,48,47,668 and so on. Using a Formula or Macro how can I get it to take each number(s) before each comma and put it in the next column on the same row?
So using the above example numbers you would end up with 1 in column A1 and 154 in column B2 and so on and so forth.
View 8 Replies
View Related
Jan 30, 2009
i'm trying to do is split the contents of the cells in column A in to 2 seperate cells. The contents of the cells in column A are like this: alpha.bravo.charlie. I want to delete alpha and have bravo as the new contents of column A and charlie as the contents of column C.
View 2 Replies
View Related
Sep 18, 2008
The columns A & B need to be split as shown in the table (3 columns as shown in D,E & F)
In column A the hrs needs removing, but there is also some spaces at the front of this text which needs removing.
Column B needs splitting as shown in the table.
******** ******************** ************************************************************************>Microsoft Excel - MAPPED SCHEDULE.xls___Running: 11.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCDEF1 4.3 hrs1-08-739677-00 U1 DC STATOR COOLANT PMP 4.301-08-739677-00U1 DC STATOR COOLANT PMP2 1.1 hrs1-08-739543-00 SW/BD ALARMS FAILED 1.101-08-739543-00SW/BD ALARMS FAILED3 1.1 hrs1-08-739544-00 ALARMS FAILED ON TEST 1.101-08-739544-00ALARMS FAILED ON TESTSheet2 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 9 Replies
View Related
Jul 13, 2009
I want to split the contents in a cell using a formula.
For Example, I have ***PavanKumar*** in A1
I want ***Pavan*** in A1 and ***Kumar*** in B1
View 9 Replies
View Related
Aug 27, 2009
Using XL07, some of my data looks like this
TY+/*-
PX/*-
TFM/*+
In an adjacent column, I am seeking to display the letters, numbers or symbols that appear before '/'. I would normally solve this little problem with something like =left(A5,2) or some combination of left and len. This method fails when the formula finds a len of 8 when the previous highest len was 7.
View 9 Replies
View Related
Feb 1, 2008
I have a spreadsheet in excel 2003 that has alot of model numbers listed in a single column with a dash. Is their any way I can remove the dash and seperate the numbers into 2 columns?
Current Spreadsheet
Model Number
199-001
200-003
210-081
This is what I'm trying to accomplish
Column B Column C
199 001
200 003
210 081
View 5 Replies
View Related
Aug 30, 2006
I have a column with adresses.
Cell A1 contents Road 12
Cell A2 contents Street 555
Now i want to split the content.
In cell A1 Road and in cell B1 12
In cell A2 Street and in cell B2 555.
Is there a macro that can do that?
View 9 Replies
View Related
May 8, 2014
I have got one column in which each cell has the complete data for the entire row.
I would like to split the data in one cell into the same row.
Got about 4000 records like it.
View 1 Replies
View Related
Feb 5, 2014
I have data in column A and Column E & F shows the required output.
View 7 Replies
View Related
Sep 23, 2012
I have data in two Columns one Columns contains data with different line in each cell
like
MR X
MR Y
MR Z
and in the other column with single line like
Success
Failed
see the below image for clear view.
In this case, I want to split the data in to each line in to each row.
Like MR X Failed
Mr Y Failed
Mr Z Failed and so on..
View 2 Replies
View Related
Jan 12, 2007
I have a datasheet that has more than 80,000 rows, Current format is:
Coulmn A - Column B
prod1 - <option selected>abc</option><option>def</option><option>ghi</option>
I want it converted into:
Coulmn A - Column B
prod1 - selected
prod1 - abc
prod1 - def
prod1 - ghi
View 3 Replies
View Related
Mar 22, 2013
I have some data from web-site, when I copy paste these data to Excel is only in 1 cell and i would like to split it into multiple rows by comma.
View 4 Replies
View Related
Jul 18, 2006
I was just not able to figure out how to write a macro for splitting the cell data into multiple rows with the other column values being duplicated. Just elaborating my problem, its like i have a column in which i have multiple values separted by a comma, So i need to divide all these values on basis of a comma and then copy this value to a new row below, along with the previous column values same for this new row. in my file cost center column is to be worked upon. Can this be done by a macro ?
View 5 Replies
View Related
Jul 6, 2014
I have a situation where I have source data formatted like this:
Document ID
Document Name
Author
[Code]....
What I would like to do is to get each author on a separate row. I am able to do that using Text to Columns using the carriage return and then doing a transpose. I've also seen some threads where VBA is used to accomplish similar. However where I am stuck at is getting everything else to drop down accordingly. I.e. with the data set above I'd like to get to:
Document ID
Document Name
Author
[Code]....
View 3 Replies
View Related
Jul 3, 2014
I want to split the contents of a single cell(ALT Enter as delimiter) into multiple cells and retain the values in column B.
Ex:
A1: apple
banana
car
house
A2: yellow
B2: building
x
y
B3: O
Output:
sheet 2:
A1: apple B1: yellow
A2: banana B2: yellow
A3: car B3: yellow
A4: house B4: yellow
A5: building B5: O
A6: x B6: O
A7 B7: O
View 9 Replies
View Related
Dec 1, 2008
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 9 Replies
View Related
Sep 12, 2012
Small piece of code to clear the contents of
Rows 33 thru 100 , then
Rows 142 thru 209 , then
Rows 251 thru 318 , then upto
Rows 43524 thru 43591 then finish
View 5 Replies
View Related
Mar 12, 2013
Every week I get a report that contains 1 or more (sub) part numbers in each row down column E, separated by a space. I need each part on a separate line, with the rest of the data on that row intact. Because these are the sub-components of an overall part, the data in column E is stored grouped together like that.
I currently use text to columns to break those up, separating them by the space, add a count of those cells so I know how many sub part numbers there are, run a 'duplicate rows' macro on that count, then manually delete out numbers so I'm left with one part number per row. The report is already over 100 rows long, so after I run the 'duplicate rows' macro I have a lot of rows to work through.
View 7 Replies
View Related
May 23, 2006
I have a spreadsheet with a serial number is row 1 in column A with the rest of Row 1 empty. Column A is empty in Row 2, but has the data associated with the row above in columns B through L of Row 2. Then comes 2 blank rows and the pattern repeats with a new serial number in column A of row 5 and so on. I would like to move the serial number down one row, delete the now empty first row, delete the following 2 empty rows, and then loop to do the same thing again for all 9000 rows of the spreadsheet.
View 2 Replies
View Related
Aug 29, 2007
I have merged two workbooks into one. What I need to do at this point is to delete all rows that have a duplicate entry, basically anytime the cell content in one cell matches the cell content in the cell right below or above it, BOTH rows should be deleted. At this point, this is above my VBA skills so I'm asking for help in how to do this. The stripped version of the workbook is attached (only 100 rows) but in reality this is a huge workbook with almost 22,000 rows.
You will notice in the attached workbook, that cell contents for A2 and A3 match. For what I need to do, I need both rows (2 and 3) to be deleted. If you go down a bit, starting in row 89 all the contents in column A are unique so those need to remain.
View 3 Replies
View Related
Apr 26, 2006
I need to identify rows where the contents of a given cell are equivalent to the cell below. I have managed to get this working based on iterating through the cells and selecting them both for cutting.
However it doesn't work effectively: It seems to be leaving blank lines in the original sheet and further fails after the first execution. I would be grateful for any suggestions on a good method to perform the following operations:
(1)to compare the cell with the cell below
I have used
If cell = cell.Offset(1, 0) Then
(2)to act on the rows for the two connected rows
Sub Test4()
' Loop through rows
Dim TheExtract As String
Dim cell As Range
'Application. ScreenUpdating = False
For Each cell In Worksheets("Sheet 1"). _
Range("A1", Worksheets("Sheet 1").Range("A65536").End(xlUp))
TheExtract = cell
If cell = cell.Offset(1, 0) Then
cell.Rows("1:2").EntireRow.Select
Selection.Cut
Sheets("Test").Select
ActiveCell.Offset(3, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown
End If
Next cell
'Application.ScreenUpdating = True
End Sub
View 3 Replies
View Related
Apr 15, 2014
I'm am trying to set up a household finances dashboard in excel - What i'd like to do is categorise & tag HH expenditure based on part of the description within a bank statement, the desired result being:
account tagging.jpg
My ideal solution would be some kind of lookup with a table containing the keywords & corresponding tags to match part of the description to, as this will allow easy management of the tags, situations where I have multiple keywords for the same tage (supermakets for example), and so forth. Is this possible?
Currently I am using a very large nested forumula which i've cobbled together based on searching for ideas. It works, but managing new tags is difficult, part of the formula i'm using is:
=IF(ISNUMBER(FIND("00111888",'JOBSFPCUR-20140414'!C4)),"Pete transfer",(IF(ISNUMBER(FIND("LAURA",'JOBSFPCUR-20140414'!C4)),"Laura Pocket Money",(IF(ISNUMBER(FIND("TESCO",'JOBSFPCUR-20140414'!C4)),"Supermarket")))))
View 2 Replies
View Related
Jul 15, 2013
I have a large set of data regarding the activities going on in particular rooms. The data contains activity name, start time, day, duration, room and size.
I would like to select all rows that contain a cell in the activity name column which contain the words 'VideoConference'.
I have tried to Search, Select All then index the remaining data using =iferror(index(etc. However some activities occur in more than one room and all variables apart from 'Room' are the same. Thus when I have tried to index the room column I get the same room for both rows.
ps the raw data set consists of +28000 rows so I can not simply look through and copy.
View 4 Replies
View Related
Mar 20, 2014
I'm sure there is an easier way than copying and pasting each individual cell from one spreadsheet to another. Is there a way I can define a batch of cells (city, state, phone #) and copy them into the other spreadsheet so I don't have to do each cell individually?
Here is a picture to show what exactly I am trying to do.
SS.PNG
Also, the cells that belong in the same column and row on one spreadsheet are equal distance from each other throughout the other spreadsheet that has all the info in 1 column.
View 1 Replies
View Related
Nov 17, 2011
i have a workbook with over 600 worksheets and any vba code to do the following.... (each worksheet contains different number of lines)
At the moment the data is in columns a to d
What i need is the data currently in cell a1 (in each worksheet) to appear beside every line in that worksheet
Then i need to take all this data and put it onto one single worksheet .
View 2 Replies
View Related
Aug 18, 2009
I have a spreadsheet that has a subtotal row after each data grouping. I need help with a macro that will go to column C, look for any cells with the word "Subtotals" in it, and format that row based on another macro I already have set up. I've tried about 50 different types of macros that I found out on the web, but none have worked.
View 9 Replies
View Related
Feb 9, 2013
I would like to have my macro code search column A (supplier numbers) and split the rows into groups of rows of 5 or less and then insert 3 blank rows between each group of rows. The split needs to start on a new supplier number and cannot split a supplier number into two different groups. Here is a sample:
Supplier
Invoice Date
GL Date
Invoice Amt
[Code].....
View 1 Replies
View Related
Mar 4, 2013
if there is a way to split multiple values in a cell to multiple rows. example check the attached sheet, macro/function with in excel etc.
View 2 Replies
View Related