Data transformation

[jakaur32]

I have attached a sample of data I am dealing with. I have data that is similar to column A and I need to separate it like it is in Columns B,C,D. I have been using text to columns, however, the issue happens around line 6 and 7. The data goes to P and B columns instead of B and C columns. Any guidance will be greatly appreciated. 

[MarqueeCrew]

@jakaur32 ,

Attachment fail :(  would you like to try again please?

[CharlieS]

The attachment worked for me so here's one way you could go about this:

I come into this assuming that if a field doesn't have all 3 "PBS" components, that the leading objects are skipped. In the attached example, I show how you can determine how many PBS components are in each record and use that information to apply a shift to any records less than the max amount. A Dynamic Rename is also used to achieve the desired final output format. 

[GeneRinas · Accepted solution]

There is probably a better way, but you could get creative with a formula and pad the extra delimiters to the left before the text to columns.  See attached.

[jakaur32]

Thank you so much, this worked out great. and i apologize for not posting this sooner.