Need a more advanced Text to Columns setup, please

[wdkwang]

So I have to create a macro for a project that's due no later than 2 days from now. The first part consists of splitting the contents, which Text to Columns normally works well for but not in this case since it is a large data set and doing touch-ups afterwards is a long and tedious process. I'd like to know if there was some function that will be able to do everything in one shot. See below for example:

Once Text to Columns is used and comes out perfectly, it should look like this:
PD: ABC Company ABC20150315 CDF20150416 123456 234567 345678 456789 bla bla bla bla bla bla bla

But there are a number of rows where it requires touchups when there was originally a space where there shouldn't have been, ie:
PD: XYZ - Company ABC 20150315 CDF 20150416 123456 456789 bla bla bla bla bla bla bla

1. In the XYZ example, there is a dash between XYZ and Company so it pushes data into the 4th column and makes the data misaligned.
2. Also, there is a space between ABC and 20150315, and CDF 20150416. This causes the data to be misaligned as well since those need to be together. How do I go about that? I've thought about concatenate but I'm not sure if it can automatically detect what rows to operate on or not.

3. the 6 digit numbers that follow after CDF20150416 can vary in amount from row to row. Some will have 2 and some may have 6 of them. Those need to be transposed clearing the initial issues in #1 and #2 in such a manner that it'll look like this:
Column A Column B Column C Column D
123456 PD: ABC Company ABC20150315 CDF20150416 123456 234567 345678 456789 bla bla bla bla bla bla bla 3/15/2015 4/16-2015
234567 PD: ABC Company ABC20150315 CDF20150416 123456 234567 345678 456789 bla bla bla bla bla bla bla 3/15/2015 4/16-2015
345678 PD: ABC Company ABC20150315 CDF20150416 123456 234567 345678 456789 bla bla bla bla bla bla bla 3/15/2015 4/16-2015
456789 PD: ABC Company ABC20150315 CDF20150416 123456 234567 345678 456789 bla bla bla bla bla bla bla 3/15/2015 4/16-2015

The 6 digit number numbers now have been transposed into their own individual rows and the original text is in 1 column again, and the ABC/CDF parts have been adjusted to reflect a date format.

Please help, and thank you.

 

[yky]

Can I assume that all the six-digit numbers occurs between "DF" and "Status"? If that is the case, you can figure out how many six-digit numbers are there by InStr(PDstring, "DF") and InStr(PDstring, "Status"). Then, you know how many rows to insert.

In the For loop, you do insertion and fill in the left six letters of the PDstring. Then you chop off the left seven letters of the PDstring.

Dim PDstring As String
Dim loopcount As Integer
Dim Six_digits As Integer 'number of six-digit numbers

PDstring = Range("B1")
PDstring = Right(PDstring, Len(PDstring) - InStr(PDstring, "DF") - 10)
PDstring = Left(PDstring, InStr(PDstring, "Status") - 2) 'Now, you have the six-digit number string

Six_digits = (Len(PDstring)+1) / 7

For loopcount = 1 To Six_digits Step 1

Next

 

[wdkwang]

Can I assume that all the six-digit numbers occurs between "DF" and "Status"? If that is the case, you can figure out how many six-digit numbers are there by InStr(PDstring, "DF") and InStr(PDstring, "Status"). Then, you know how many rows to insert.

In the For loop, you do insertion and fill in the left six letters of the PDstring. Then you chop off the left seven letters of the PDstring.

Dim PDstring As String
Dim loopcount As Integer
Dim Six_digits As Integer 'number of six-digit numbers

PDstring = Range("B1")
PDstring = Right(PDstring, Len(PDstring) - InStr(PDstring, "DF") - 10)
PDstring = Left(PDstring, InStr(PDstring, "Status") - 2) 'Now, you have the six-digit number string

Six_digits = (Len(PDstring)+1) / 7

For loopcount = 1 To Six_digits Step 1

Next

thanks so much for your reply. i'll give it a shot