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]

1. It seems to me you just do a replace first to remove "- ".
2. I suspect "ABC Company" is probably not a real company name in your data. So, it is difficult to suggest a solution.

 

[wdkwang]

1. It seems to me you just do a replace first to remove "- ".
2. I suspect "ABC Company" is probably not a real company name in your data. So, it is difficult to suggest a solution.

HI thanks for replying. Could you elaborate more on #1? Not sure what you mean by it. Also ABC Company is for security purposes of course but the gist is to have an automated smart macro to know when to combine certain fields that were not organized well in the raw file

 

[yky]

HI thanks for replying. Could you elaborate more on #1? Not sure what you mean by it.

Something like:

replace(A1,"- ","")

which searches "- " in A1 (or whatever the company name resides) and removes it (replaces it with ""). Then, the string (company name) would become "normal".

Also ABC Company is for security purposes of course but the gist is to have an automated smart macro to know when to combine certain fields that were not organized well in the raw file

In the example you give, we can always search "ABC " and then check if the following letter is C. If it is, that means we have found "ABC Company". In this case, we leave it alone. If not, that means we have found "ABC ?" (where ? is a digit). We then replace "ABC " with "ABC". This way, we convert "ABC " to "ABC" and your problem is solved. But it only works for the example you give and will not work for your real data because "ABC Company" is not a real company name.

 

[wdkwang]

Something like:

replace(A1,"- ","")

which searches "- " in A1 (or whatever the company name resides) and removes it (replaces it with ""). Then, the string (company name) would become "normal".

In the example you give, we can always search "ABC " and then check if the following letter is C. If it is, that means we have found "ABC Company". In this case, we leave it alone. If not, that means we have found "ABC ?" (where ? is a digit). We then replace "ABC " with "ABC". This way, we convert "ABC " to "ABC" and your problem is solved. But it only works for the example you give and will not work for your real data because "ABC Company" is not a real company name.

hey yky, thank you for that. suddenly that particular issue became so clear.
Do you have a suggestion in terms of the transposing question?
To reiterate, the 6 digit numbers follow "CDFxxxxxx" need to be transposed into a preceding column. the function has to be dynamic in how it can adjust if the particular line has either only 1 6-digit number or 20 6-digit numbers. Any bit of help is greatly appreciated. Thank you again

 

[yky]

I'm sorry. I still don't get it. Are the 6-digit numbers in columns, one in each column, and in the same row? So, if there are five six-digit numbers, they'd occupy five columns of the same row?

 

[wdkwang]

I'm sorry. I still don't get it. Are the 6-digit numbers in columns, one in each column, and in the same row? So, if there are five six-digit numbers, they'd occupy five columns of the same row?

Correct on both parts.: The 6 digit numbers are going to be in the same row and in separate columns (after using Text to Column). And if there are 5 6-digit numbers, they'll be transposed to occupy 5 columns for that specific line item. This needs to be dynamic so it can adjust for how many 6-digit numbers there'll be before moving onto the next line item. See below:
Column A Column B Column C Column D
PD: Albert's Company BC20150315 DF20150416 123456 234567 345678 456789 Status: 3/15/15 4/16/15
PD: Dan's Company BC20150316 DF20150417 987654 987654 Status: 3/16/15 4/17/15

Column A Column B Column C Column D
123456 PD: Albert's Company BC20150315 DF20150416 123456 234567 345678 456789 Status: 3/15/15 4/16/15
234567
345678
456789
987654 PD: Dan's Company BC20150316 DF20150417 987654 876543 Status: 3/16/15 4/17/15
875643

So in the second list, you can see how the 6 digit numbers of the 1st line item has been transposed into Column A and dynamically allowed the needed amount of spaces for the 2nd line item to do the same. I thought about somehow integrating using the "Status:" part in Column B for an IF/Then function for excel to determine whether to create another column to transpose or not. I'm really lost in how to put this together.

 

[SpillerBD]

You probably need to go to more of a line-by-line import of the txt file.
With the multiple cases for how to treat each line that is how I work with various text reports that have 'transactions" recorded in a multi-line format.
It appears that a flag you do have to work with would be where the " CDF" occurs... (?)

 

[wdkwang]

hi yky, just making sure, but I spend another 10 minutes editing the response above this one in case you looked at it when I first sent it

 

[wdkwang]

You probably need to go to more of a line-by-line import of the txt file.
With the multiple cases for how to treat each line that is how I work with various text reports that have 'transactions" recorded in a multi-line format.
It appears that a flag you do have to work with would be where the " CDF" occurs... (?)

Hi Spiller, thanks for replying. The initial spacing issues with "CDF" in the original post is taken care of now with a "Find and Replace" function, but the main transposing question still remains.