Data transformation

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.

test data.xlsx

Preparation

Documentation

Accepted answers

GeneRinas

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.

Pad_Delimiters.yxmd

All comments

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.

20190603-SplitandRename.yxmd

GeneRinas

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.

Pad_Delimiters.yxmd

jakaur32

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703