Splitting a text column with multiple entries from 1 to Many

[Chezzer14]

Ken - Sorry for the confusion on the blog. I am extracting information from Exchange, and I can pull it in and filter down without issue. Now I have the body of the text for multiple e-mails that comes through as shown for one record below. I split out the Ticket Number easily, but I would also like to split out each of the remaining groups of lines into their own columns associated with the ticket number. I started splitting them out further, but quickly realized these groups for each Serial Number identified on the ticket can range from a single unit to many.


I am still the novice, but is there a tactic for dividing these variable number of groups of data of varying lengths into there own individual records?

I have looked at splitting as I said, and other options of the main menu, as well as looking for snippets of M-code, without success. Any push in the right direction to get me started would be very appreciated. Thanks!

Ticket Number 1983

Occurrence Date: 4/25/2016
Facility: Nashville, TN
Serial: PKNXXRPBPZ
Failure Category: Packaging/Kitting
Failure Detail: Carton to Serial Match
Region: South
Spoke: South - Tuscaloosa
Submitted By: ASMITH

Occurrence Date: 4/25/2016
Facility: Nashville, TN
Serial: PKNXXRRCLQ
Failure Category: Packaging/Kitting
Failure Detail: Carton to Serial Match
Region: South
Spoke: South - West Monroe
Submitted By: ASMITH

 

[Brian Smith]

Do they come through as carriage returns when you import the data? I would have to look up how but you can split columns by carriage return.

 

[Chezzer14]

Yes they do. I did find some code for splitting by cr/lf but I have not had success with it.

 

[JimBobBowie]

Hi Chezzer,

I am going t assume that you get the data and can paste it into column A as shown. I have attached a simple spread sheet with a macro to list the items in to their own columns.

JimBob

 

[Chezzer14]

Thank you JimBob -

I am not sure if I described my problem well. The format I originally posted is the format as shown in the body of the e-mail. Where I am actually at is shown on tab 2 of your file. I use PQ to read an Outlook folder, pull in the contents, filter it down to the most recent month, remove extra columns, I split out the Ticket#, and I am left with three columns. DateTimeSent, Ticket#, and TextBody.2. In the TextBody .2 column is the mashup of the e-mail contents that pastes into an Excel cell as one long string like this:

Occurrence Date: 4/25/2016CTDI Facility: Nashville, TNSerial: PKNXXRPBPFailure Category: Packaging/KittingFailure Detail: Carton to Serial MatchRegion: Big South RegionSpoke: South - West MonroeSubmitted By: AJOHNSONOccurrence Date: 4/25/2016CTDI Facility: Nashville, TNSerial: PKNXXRRCLFailure Category: Packaging/KittingFailure Detail: Carton to Serial MatchRegion: Big South RegionSpoke: South - West MonroeSubmitted By: AJOHNSON

But it pastes in to a document like this:

Occurrence Date: 4/25/2016

CTDI Facility: Nashville, TN

Serial: PKQNGPMXX

Failure Category: Packaging/Kitting

Failure Detail: Carton to Serial Match

Region: Big South Region

Spoke: South - West Monroe

Submitted By: AJOHNSON



Occurrence Date: 4/25/2016

CTDI Facility: Nashville, TN

Serial: PKQNHDHND

Failure Category: Packaging/Kitting

Failure Detail: Carton to Serial Match

Region: Big South Region

Spoke: South - West Monroe

Submitted By: AJOHNSON

 

[JimBobBowie]

Hi Chezzer,

Was my output as a table what you are looking for? If so I can work out something to take the data as in sheet two and convert it into a table.

Jim Bob

 

[Chezzer14]

Jim Bob -

Yest that is exactly where I was hoping to land.

Thank you!

 

[JimBobBowie]

Hi Chezzer,

I have got something that looks OK but your data in row 3 is too long that it has been truncated. My solution falls over when the data runs out early. Can you paste he data into word or notepad and send it as a flat file?

JimBob

 

[Chezzer14]

Yes I can. Thank you!

 

[JimBobBowie]

Hi Chezzer,

I have had a look and it will work OK. You will need to paste the data into a text file and then open Excel That will split the data into lies and you won't lose any. They we can process it into a table.
I have ben very busy today but I will try to send you something tonight from home.

Jim Bob

 

[Chezzer14]

Thank you Jim Bob. I appreciate your effort and assistance.

 

[JimBobBowie]

Hi Chezzer,

I think I have it worked out for you Have a look at the attached file. There is a simple process to follow outlined ne one of the sheets.

Let me now how you get on.

JimBob

 

[Chezzer14]

Jim Bob -

This works very smoothly. Thank you very much for your help and time.

Have a great weekend,

Rick

 

[Bill Szysz]

Hi @Chezer,
Since we're in the section PQ, so this is my solution by PQ ;-)
It based on Chezzer3.xlsm file from @JimBobBowie ("Paste Here" sheet) because i did not have the file with a raw data.
Try if you want.

Regards