data in one column into several columns with certain rules

Hey Guys-
I've looked around the forum and couldn't quite piece together what I need for this problem I'm having. I have minimal understanding of macros - probably a 2 week portion of a tech class I took years ago.

I have data that is all within column A, but I need it to be separated into 14 separate columns.

The data looks like this:
*note xx's denote numbers

11 - Product ID1
----------------------------
1234567 AA A xxxx-x0 String1 String2(Company Name) String3 xxxxx1 01/11/11 01/11/13 02/13/12 xxxx2 03/13/13 xxxx3

12 - Product ID2
----------------------------
etc..

As you can see the data is actually spaced out nicely, however, the strings may have extra spaces and that throws off the text-to-columns function.

-Not all columns will have values
-the string values may have one word or multiple words(with spaces and/or -)
-The Product ID titles along w/ the dotted lines should be skipped (remain the same)

Please help - information would be greatly appreciated to ease the headaches of my job. THANKS!

It would help if you posted a real example of what the result is supposed to look like instead of a description.

You did mention the "text to columns" function. There is a check box in that funciton for "Treat consecutive delimiters as one" which will cause multiple spaces to be treated as one space. If multiple spaces are the issue, checking the box sould solve the problem.

Otherwise, I'd understand better with a real example.

Originally Posted by Frasterist

It would help if you posted a real example of what the result is supposed to look like instead of a description.

You did mention the "text to columns" function. There is a check box in that funciton for "Treat consecutive delimiters as one" which will cause multiple spaces to be treated as one space. If multiple spaces are the issue, checking the box sould solve the problem.

Otherwise, I'd understand better with a real example.

I've attached something more pertinent. There's a single space so consecutive delimters option would not be valid. you can pretty intuitively see where the columns should go. "available now" should be in one column not 2. I'm not sure if this would be a difficult macro to make.

It would help if you provided sample results of what you want. Drop down a few rows and separate the text string(s) into individual cells. That way, it isn't open to interpretation.

Originally Posted by Frasterist

It would help if you provided sample results of what you want. Drop down a few rows and separate the text string(s) into individual cells. That way, it isn't open to interpretation.

i just realized the other set had extraneous data, here's a cleaned up version with the desired output on the 2nd sheet.

I played around with this for awhile; but, there are problems in your presentation. It is not consistant. On sheet one, we see:

(number)(A)(number)

Then on sheet 2, we see:

(number)(A)(M)(number)
If you want a space or cell left blank, put an irrelevant character in there like two quotation marks "", something that will never occur normally. The code can remove that while processing.

In some cases, we see (text space hyphen space text) while in other cases we see (text hyphen text) without spaces before or after the hyphen.

On sheet 2, column F, the name is not formatted like the name in sheet 1, row 6.

To make this work, I believe the code would have to take into consideration every possible variation to your raw text string(if this do this or if that do that); and, from what I have seen so far, it would be impossible to anticipate every possible variation without seeing all of them to begin with. In the end, I doubt that would even work because there would be more inconsistencies and variations introduced in the future. Code can't read minds.

As I see it, your best bet is to use the "text to columns" function and then go through and manually fix the errors. That's the price we pay for not being consistant to begin with and probably why no one else is fiddling with this.

Mac_Ro

Are you sure with your latest attachment?

I can not find 526476 in sheet1.