Split Cell by Delimiter, Move to New Row...

**jpfulton** · 09-03-2008, 11:08 AM

Help would be greatly appreciated. I'm really struggling with this. 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), how hopeless I am without outside help. THANK YOU!!!! in advance.

Pete

**Leith Ross** · 09-03-2008, 12:38 PM

Hello jpfulton,

Welcome the Forum!

I have a couple of questions about the data. You say the data in column "I" is delimited by "$" characters. If the entry is a single value will it always be terminated with a "$" also? Will there be any blanks in this column? Which version of Excel are you using?

Examples
1) "Data$
2) "Data"

Sincerely,
Leith Ross

**mikerickson** · 09-03-2008, 01:06 PM

Perhaps something like

Please Login or Register  to view this content.

**jpfulton** · 09-03-2008, 01:10 PM

Originally Posted by Leith Ross

Hello jpfulton,

Welcome the Forum!

I have a couple of questions about the data. You say the data in column "I" is delimited by "$" characters. If the entry is a single value will it always be terminated with a "$" also? Will there be any blanks in this column? Which version of Excel are you using?

Examples
1) "Data$
2) "Data"

Sincerely,
Leith Ross

Thanks for responding.

Single values will not be terminated with a $.
The column will contain some blanks.
The version of excel is 2003.

**jpfulton** · 09-03-2008, 03:40 PM

mikerickson,

tried your code (this is probably a simple fix), and it doesn't work on my workbook. chokes with error "Run-time error '9'": Subscript out of range then it shows me that line 5 "Set DestinationSheet" is the problem. Strange to me b/c I'm running it on a workbook with a sheet called "Sheet2" ???

Any idears?

**mikerickson** · 09-03-2008, 06:26 PM

Is there a Sheet1? Sometimes the line after the problem is highlighted.
Also, my code looks to Column A. It needs to be modified to Col I (.Cells(1,9))

**jpfulton** · 09-03-2008, 06:30 PM

Yep... there's a Sheet1 as well.

I'll be sure to adjust the code to point to column I instead of A, but I imagine that this error shouldn't appear as a result of that???

I'll check it again tomorrow when I have the file in front of me...

thanks!

**jpfulton** · 09-04-2008, 08:55 AM

I've adjusted the part of the code that references Column A to refer to Column I... still no fix... I'm still getting the same error as shown above...

Any other ideas??

thx

**mikerickson** · 09-04-2008, 09:12 AM

The spelling of the sheet name should be checked, perhaps there is an unwanted space.
Also this code refers to the name that is on the sheet tab, not to the code name.

**jpfulton** · 09-04-2008, 09:18 AM

Originally Posted by mikerickson

The spelling of the sheet name should be checked, perhaps there is an unwanted space.
Also this code refers to the name that is on the sheet tab, not to the code name.

I retyped that entire line in the code you supplied and then deleted and created a new tab called Sheet2... case sensitive and all... grrr...

I'm going to try a new sheet name (guess I should've tried that before)...

This also errors out on a blank workbook

**jpfulton** · 09-04-2008, 09:30 AM

okay... i'm trying to debug by simplifying

I've taken little steps toward complete simplification (still getting errors along the way).

I'm at the point now that I am working with a new workbook, 1 blank worksheet, 4 lines of code, and still getting the error...

Will somebody else try this code please and tell me if they get the error?

Please Login or Register  to view this content.

Same error, whether I have two worksheets, regardless of what I call them, etc.... I have tried everything I can think of.

**mikerickson** · 09-04-2008, 09:50 AM

Try

Please Login or Register  to view this content.

If that doesn't work, manualy selecing Sheet2 and running this might be enlightening.

Please Login or Register  to view this content.

**jpfulton** · 09-04-2008, 10:11 AM

i feel like i'm getting post-happy... so forgive me...

but a few minutes ago, I discovered that the problem appears to be switching worksheets... I changed the destinationsheet and the sourcesheet both to Sheet1... no errors

I changed:

Set SourceSheet = ThisWorkbook.Sheets("Sheet1")
Set DestinationSheet = ThisWorkbook.Sheets("Sheet2")

To:

Set SourceSheet = ActiveWorkbook.Sheets("Sheet1")
Set DestinationSheet = ActiveWorkbook.Sheets("Sheet2")

And that runs without errors... not sure if it works right (seems that it might now)... but maybe cause I haven't readjusted for Column I

*******EDIT*************

It worked... now i've closed the workbook and re-opened... i can't get it to work again

I feel like i'm losing my mind.

**rwgrietveld** · 09-04-2008, 10:14 AM

Why not add the excel file?

Try this:

Please Login or Register  to view this content.

**jpfulton** · 09-04-2008, 10:26 AM

okey dokey... ha... I had to delete the last 19,000 rows... but this will probably give you enough of an idea... good ol' card catalog

**jpfulton** · 09-04-2008, 11:09 AM

Okay, I've attached a before and after of part of the file that I have manually converted (and color-coded). Note... Column I has been moved to Column K. Column K has been split, then if there is data in any of the columns to the right of Column K as a result of the split, the data is moved down to the next available row. Once all of the moving of the split column is complete, the rest of the ORIGINAL ROW's data is copied down until a row that is pre-existing is found.

I'm thinking the files will explain this better than my words possibly can.

thx again.

**jpfulton** · 09-05-2008, 11:06 AM

Does anybody know where I can cross-post this? I'm really desperate for help... I figured with my latest attachments somebody would be able to help.

**shg** · 09-05-2008, 11:35 AM

I haven't read the whole thread, but perhaps this:

Please Login or Register  to view this content.

**jpfulton** · 09-05-2008, 12:15 PM

THANK YOU so much! It appears to work perfectly. I might need a couple variations. I might be able to figure out how to tweak it if someone can take that code and adjust it for the following... I will attach LCtest1.xls which will be the before and LCtest3.xls which will be the after (you will notice the slight difference between the LCtest2.xls that was attached before and LCtest3.xls that I'm attaching now).

Note on LCtest3.xls: In the title column the new rows are to be filled in with "See also [reference to originating subject field] then part of the pub info field [date published]"

I understand that pulling out a portion of a field in completely inconsistent data, is a tall chore... If code can be written to ROUGHLY accomplish this, I can do some creative find and replace+manual work to get it done.

again... thank you!