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 ?

ADVERTISEMENT

Split Cell Into Multiple Rows

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

Split Multiple Values In A Cell To Multiple Rows?

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

Split A Cell Of Data Into Multiple Rows?

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

Split Excel Cell With Carriage Returns Into Multiple Rows?

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

Split Single Text Cell Into Multiple Rows Using Comma Delimiter?

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

Split Single Text Cell Into Multiple Rows, Using A Comma Delimiter

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

How To Split Data Of A Shipment Into Multiple Rows

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

VBA Code To Split Multiple Customer Rows?

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

Split Line Break Multiple Columns Into New Rows?

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

Split Single Column Into Multiple Columns And Rows

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

Split One Cell Value Into Rows?

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

Split Cell Data Into Rows?

Feb 5, 2014

I have data in column A and Column E & F shows the required output.

View 7 Replies View Related

Split Data In One Cell Into Below Rows?

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

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 View Related

Split Cell Contents To New Rows 50 Characters In Each Row

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

Split Value In A Cell Into Multiple Cells?

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

Split Information In One Cell To Multiple Cells?

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

Macro To Split Multiple Valued Cell?

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

Split Multiple Dates Stored In Single Cell

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

Macro To Split Rows Into Groups Of 5 And Insert 3 Blank Rows In Between

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

Split Range Of Numbers From One Cell Into Column Of Multiple Cells All With Individual Numbers

Mar 5, 2012

I have a mass of data which look something like this:

table removed

and I require the ranges of reference numbers to be listed in a column one above the other, which requires inserting new rows. I also need the date & description columns copied down into the newly inserted rows.

So basically for example I would want the top row to now read:

table removed

and then apply the same procedure to the other ranges below this.

View 4 Replies View Related

Split Row Into 2 Rows IF - VBA

Jun 11, 2013

I am putting together a macro that takes hours worth of work away by doing all the calculations and splitting.

I have managed to do all the basic stuff but this is a little more complicated.

My spreadsheet has a couple of thousand rows of data, i need the macro to check for the following:-
If DATA14 (Column N) = YES and RATE (Column D) = 21.81

I need this line splitting into two to replace the original. (would be better if i could move the original data to another sheet to show the ones that have been split)

One with 87% of the rate
One with 13% of the rate (Data8 will always = OLHA on this row)

Then the total will be recalculated based on the new rate.

View 1 Replies View Related

Excel 2010 :: Multiple Entries In One Cell That Need To Be Spread Across Multiple Rows

Jun 17, 2014

I need to convert data from column IDS into separate rows, all other columns need to stay in tact. There are several distinct patterns for the IDS column, main identifiers are always starting with FILER or TEAL and the trailing numbers behind it have no more than 6 digits.

BEFORE MACRO

ID
AREA
TYPE
CLASS
QTY
IDS

1
COAL
TYPE9917312
CLASS881345
2
FILER756911**/**FILER123188 ^** FILER877119*118

[Code] ........

AFTER MACRO

ID
AREA
TYPE
CLASS
QTY
IDS

1
COAL
TYPE9917312
CLASS881345
2
FILER756911

[Code] ......

What the MACRO would look like? This is for Excel 2010.

View 3 Replies View Related

Split CSV File Into Rows?

Feb 14, 2014

A have a CSV file which contains a string of data that has a repeating pattern e.g

1234561234560N12343159753 00Company 123 Mr Smith 9874561236540N12343159753 00Company 432 Mr Jones 654987159753680N12343159753 00Company 431 Mrs A Bruce

The first long string of numbers are bank details (Sort Code, Account Number) followed by our company bank details then company name, then a reference number then the name of the account holder.

There are over 20000 records all in one long string of text and all contained within cell A1. There are also a few occasions when there is no space between the name of the account holder and the start of the next persons bank details.

What I need is for there to be a split after each account holder's name and for each persons details to be on a new row e.g

1234561234560N12343159753 00Company 123 Mr Smith
9874561236540N12343159753 00Company 432 Mr Jones
6549871597530N12343159753 00Company 431 Mrs A Bruce

Ideally I would also like the first string of information to be split in to columns so that it reads as so -

123456 123456 0N12343 159753 00Company 123 Mr Smith
987456 123654 0N12343 159753 00Company 432 Mr Jones
654987 159753 0N12343 159753 00Company 431 Mrs A Bruce

The first collumn would contain the first 6 digits, the 2nd would always contain the next 8 etc. I have tried using the Text to Columns function but it gives me seperate columns for initals etc when I would like the full name and title in one column.

View 5 Replies View Related

Split Rows By Bold Text?

Oct 7, 2013

I have an Excel sheet with 2 columns : Date and TextColumn

The TextColumn column contains in each cell text such as this :

Title1
TextLine1
TextLine2
Title2
TextLine3
TextLine4
....

I need to split each line as follows : Each title (in bold and on a new line) and the text who follows should be placed in a new row, copying the date in each new row created.

View 1 Replies View Related

Separate Multiple Lines In Cell To Multiple Rows?

Jul 10, 2014

I have a challenge where in have a list of about 4000 rows and each cell have mutiple lines. ( i know there is a thread about this but that did not address my issue).

So my data is as follows - (I have attached teh sample file)

"Pari bom - GP and CFO 02547895452 pari@gmail.com

papa bom - GP and CFO 23365789541 papa@gmail.com

mama bom - CFO 2587412589 pari@gmail.com

nana bom - CFO 2587412589 nana@gmail.com

masi bom - CFO 2587412589 masi@gmail.com

"

I want to be in a position to seperte each line in exclusive rows and then seperate teh name, number and the email ... the later eing easier but cant get my head around seperating the cells.

View 4 Replies View Related

Split Multiple Names

Aug 1, 2007

I have and issue where I have name in one cell and need to separate them into 2 cells. Trouble is it is a combination of single names, husband and wife, and partners with different surnames. Sample of data is below:

Belinda Smith & Grant Jones
Janine
Ken & Marie Smith
Louie & Betty Johnson
Naomi Thomsoon & Craig Brown
Narele & Barry Day
Nicholas & Julie Smithson
S M Bole & Co
Jim Morris
Danielle & JohnSimpson

View 9 Replies View Related

VBA - Split Comma Separated Entries To New Rows?

Feb 4, 2013

I have a relatively large data set that is separated into columns A-G. In column G, I have a multitude of values that are separated by commas. What I would like to do is to separate these entries into rows while keeping those values in A-F constant.

Here is an example of what I would like to do through VBA:

ex1.jpg

to

ex2.jpg

Also, kudos. This website has now gotten me interested in VBA.

View 6 Replies View Related

Split String Of Data Into Columns And Rows

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

Prevent Text Being Split Down Rows On Import

Nov 22, 2007

I have a large sheet that I am having some problems with. The information comes from our database. In column A I have a client code and other information over to column D. In columns E I have dates. These dates have comments that are associated with them entered in column F. The problem is that sometimes these comments wrap into the next cell below. I have done this in the past. But the problem is that in column G there are dates of when the action took place, the issue is that there might be 1 or 10 of these dates per client. And then in Column H there are comments as well that spill into the next cell below.

What I need is to have everything in the first line, the client code over to column E, Combining of the cells in column F that were broken over multiple cells, then in Column G the first date and its combined actions to the right. The next row might have no client code but might have another date in Column G and comments in column H.

This is really hard to explain what I am looking for exactly. I have attached a small file that has an example of what might be downloaded and then a sheet of what I need it to look like when it is done.

View 8 Replies View Related

One Cell With Multiple Lines Into Multiple Rows?

Jun 24, 2010

I need a macro that will seperate multiple lines in more than one cell into multple rows.

Cells G-N contain multiple lines of data that I must split out into multiple rows; the multiple lines of data must be parced out into the same row.

View 4 Replies View Related

Split Worksheet, Seperated By Blank Rows, Into Several Worksheets

May 26, 2009

how to split one large worksheet into several worksheets using VBA. The only criteria I have to go on in the large worksheet is the existence of blank rows between each chunk of data I want on it's own worksheet.

Please see the attached zip file. There are 39 chunks of similar (but varying in number of rows) data that are separated by 3 blank rows, so I want my workbook be split into 39 new worksheets. My goal is to get the data into list/table format so I can work with it more easily and export into Access etc.

Also, just out of curiosity, is it possible to convert a HTM file into CSV? The attached data starts life as a HTM file that I just convert to XLS

View 6 Replies View Related

Split Worksheet Into Multiple Worksheets

Aug 12, 2009

This code splits a worksheet into multiple sheets (based on sales person in column A). But when it does it, it deletes the sheets and then re-creates them.

However this messes with my formulas I have linked to the split sheets and turns them into #REF! errors.

View 5 Replies View Related

Split Worksheet To Multiple Worksheets?

Mar 1, 2013

I would like to split a worksheet to multiple sheets based on a column header. On browsing through the forums I found the VBA code below.

My problem with is that the code automatically uses only the first column for spiliting into different sheets. But I would like to modify this so that it searches the first row for a matching header specified by me (Eg "Name" , which may be column 10)

Code:
Private Sub PagesByDescription()
Dim rRange As Range, rCell As Range[code]....

View 4 Replies View Related

Split Worksheet To Multiple Worksheets

Nov 8, 2008

I have a worksheet that has data in columns A to N. The Salesmans number is in column A and I need to split this first worksheet in to separate worksheets in the same workbook for each salesman.

View 9 Replies View Related

Split A Column Into Multiple Columns

May 4, 2009

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 9 Replies View Related

Split Workbook Into Multiple Workbooks

May 31, 2007

I'm attempting to take a Workbook that contains 1000 Worksheets and split it into 2 separate WB's of 500 WS's each ... basically half it. This could also apply to a WB that contains less than 1000 WS's, but it is highly unlikely it will ever contain more than 1000 WS's.

I'd like each new WB created to be saved in the original path with the original filename followed by a "1", "2", "3" or "a", "b", "c", etc. For example:
Original Filename = ImpactAnalysis.xls
New Filename 1 = ImpactAnalysis (1).xls or ImpactAnalysis (a).xls
New Filename 2 = ImpactAnalysis (2).xls or ImpactAnalysis (b).xls

I've located 2 modules in the forum that are a good start, but each creates a separate WB for each WS, which is a bit cumbersome with up to 1000 WS's in the original WB.

Sub splittest()
Dim sht As Worksheet
Dim w As Worksheet
Application. ScreenUpdating = False
Application.DisplayAlerts = False
For Each sht In Worksheets
Set neww = Workbooks.Add
sht.Copy neww.Worksheets(1)
With neww .....................

View 9 Replies View Related

Split Data In A Cell And Get The Value For The Split Data From A Different Table

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

Split Up Rows In Worksheet Based On Value In Column And Rename New Sheet To Same Value

Jul 9, 2012

I'd like to split up the rows in a worksheet based on the values in one of the columns. Also, I'd like the sheets to be named after the values in the column. I have attached example excel sheets to explain this better. I think the vlookup and Sheets.Add and ActiveSheet.Name formulas can be used but I'm not quite sure how to put them together. The actual data has about 20 columns and about 500 rows.

View 6 Replies View Related

Import CSV Into Worksheet Split Into Columns Then Copy Rows By Criteria

May 26, 2009

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 7 Replies View Related

ADVERTISEMENT