Split Cell Data In Multiple Rows
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
ADVERTISEMENT
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
Aug 18, 2014
I want to split data of a shipment into multiple rows, based on the a standard pallet quantity
E.G.
1) SKU: 807020450043BATCH: 41826790QTY: 15600
(PALLET QTY 1560)
2) SKU: 807580450044BATCH: 41452190QTY: 5760
(PALLET QTY 576)
I want to split the row by the pallet qty, will result in 10 rows of 1560 / 576 Data will look like this
8070204500434182679015600
807580450044414521905760
View 3 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 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
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
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
Feb 2, 2012
I currently have a number of data sets relating to customer's and values, eg.
Customer 1 12 12 12 12 12
However within this data set I also have a number of items where different customers have been grouped but are separated with a “/”, eg.
Customer 1 / Customer 2 / Customer 3 12 12 12 12 12
I want to be able to, find these rows, and break them out so that each customer with relating data is on a separate row, eg.
Customer 1 4 4 4 4 4
Customer 2 4 4 4 4 4
Customer 3 4 4 4 4 4
View 7 Replies
View Related
Sep 17, 2013
I'm having troubles with a spreadsheet and unfortunately don't know enough VBA to fix it. I have two columns, each with data like so (standard text) separated by a line break:
Object1
Object2
Object3
value1
value2
value3
And I need them separated like so:
Object1
Value1
Object2
Value2
Object3
Value3
with other items in the row from other columns applied to the new rows accordingly.
Any script/macro/tool (even non-Excel) to do this effectively (over 800 rows)?
View 3 Replies
View Related
Aug 20, 2008
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 4 Replies
View Related
Jul 31, 2006
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 9 Replies
View Related
Feb 22, 2010
I am looking for a way to split a large string (400 numeric value's, split by comma's) into a 20x20 field.
If possible, the field should be 600x400 pixels (30x20 squares), but this is just extra
Is there a way to do this in Excel (or any other program)?
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
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
View Related
Jan 26, 2012
I have an address in one cell in excel. Street/City/State/Zip are all separated by two spaces like this: 123 Anywhere St Chicago IL 60607 United States
I want to break them out into separate columns. The "Text to Columns" won't work because there are no commas or dashes separating the data.
View 3 Replies
View Related
Jul 12, 2012
I am working with a large table of quite simple data in the following format.
Ref1 SurnameInitialsDOL1 Reference2
Zz719147GLAWSJ07/04/19780271947X
YA414253CDORCE07/04/19870282179F
I need to split up the "master" table so that for all rows where DOL1 falls with a year range of e.g 6th April - 5th April (in any year) then this row is moved or copied into a new tab based on the year range.
i.e. The first row should appear in a new tab for the range 6th April 1978 - 5th April 1979.
The marco should be ale to create and name new tabs. The tab name will be 78_79.
The second row should appear in the 87/88 tab.
I have several thousand rows of data with dates ranging from 1978 - 2012 so there should be tabs populated for every year from 78-2012.
View 6 Replies
View Related
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
Mar 12, 2014
We are an office furniture company and we want to hold a master list of all stock. The columns we require are:
Product ID
Product Description
Product Grade
Product Price
Sold
The Sold column would be a YES or NO answer, and based on that I want the data to split into two additional worksheets, one with all the unsold items and one with all the sold items. So really I need to know how to split the data based on whether the line has a YES or a NO in the sold column.
The data needs to update instantly so if I change a product from unsold to sold it then comes off the current available stock tab.
View 3 Replies
View Related
Jan 17, 2014
I have tried to split using KuTool and ASAP Tool but they dont work. They can only split base on #of row. Between each of the group of my data, there is a empty row that split them. I want to split every single row from A:H to a new workbook. Why A:H becuz from A1:A8, the A5 is empty. It has to be in range, specify by me, so if the condition is met as empty from A:H, then split from there and keep going on the whole spreadsheet.
View 8 Replies
View Related
Feb 4, 2008
I have a huge Excel spreadsheet going into the 10 000 mark.
What I want to do is split the spreadsheet into multiple worksheets based on the Company column.
I've attached a small sample.
View 4 Replies
View Related
Jun 11, 2008
I have a few CSV files that sits in a folder ie C:/Data and I want to split the data in those files into different sheets. Each of those files contains rows of data with an account number. For example, I have 3 files that contains transactions and they have accounts "Orange", Apple, pear, I want to put all transations with account apple together and all Orange transaction together etc etc.
View 3 Replies
View Related
May 6, 2013
Other than using text to column feature, how to split values in a cell to multiple cells. For example; in cell A1 I have something like this:
text1
text2
text3
text4
There is no comma or seperation. What i want to do is:
B1: text1
C1: text2
D1: text3
E1: test4
View 2 Replies
View Related
Jul 30, 2013
I'm pasting a lot of data into a spreadsheet and then using a macro to split the data into three different sheets/tabs. I'm doing this by simply copying entire columns. Each sheet/tab has a headings row and autofilters added so that my boss can filter on certain manufacturers in one of the columns.
The problem is that when he selects a manufacturer, at the bottom left corner of the screen it shows the number of records, which at the moment will be something like "6 of 65211".
The actual sheet only has a few hundred rows (not 65211). So how do I delete all the blank rows beneath my data using a macro or vba code?
As an extra point, I'd like to use column B to check for blank rows (not column A).
View 6 Replies
View Related
Jan 13, 2014
I am receiving a new report from an external system. Unfortunate the system delivers many information within one cell, and it is not possible to reconfigure. These information are separate by a semi-colon or a semi-colon + space. separate the information in separate cells?
View 1 Replies
View Related
May 3, 2012
I have Column D containing multiple values that I need to split. The structure is:
Text Text (XYZ) 123 AB C
Text 456 C
Text Text (RST) 7 A CD EF
Text 22
I'd like is to split the cell using a macro rather than formulae so that the resulting cells can be filtered.
The output would be:
Text Text (XYZ)|123|AB|C
Text|456|C
Text Text (RST)|7|A|CD|EF
Text|22|
So the first split would need to be when the number is reached, the second split after the number and then subsequent splits each time a space is encountered (although there may not be any values after the number!).
View 6 Replies
View Related
Aug 25, 2009
I have a large spreadsheet which I need to split into individual workbooks by reference to a particular column.
Rather then doing this manually and splitting the data out one at a time - I would like a macro to do this for me.
I am a novice excel user.
View 9 Replies
View Related
Jun 14, 2008
how to read mutiple values from one cell. For example i want to have one cell for site visits, and there may be more than one. So i want to let the user enter more than one date seperated by a ",". Is there anyway of going about this?
View 5 Replies
View Related
