Splitting string column by column width - but skipping positions

[hrbarry]

I am trying to split a text file that is delimited by the "pipe" character "|"

Example:

|NO|Street    |ZIP   |Address2    |

The text to column tool works just fine except that there are rare occasions where a field of data actually contains the pipe and we do not want a split at this point.

Example:

|NO|Street    |ZIP   |Address | 3 |

We know the number of characters in each column (2, 10, 6 & 12 above) and began with a Regex (...)(...........)(.......)(.............)

We are still left with the pipe in the above of course and would then have to remove it as a leading character - is there a more elegant way to use regex or another tool to:

• skip a character
• create a two-char column
• skip a character
• create a 10 c

[binuacs · Accepted solution]

@hrbarry 

[hrbarry]

thank you.  the unpivot then pivot to get rid of the pipes was something i wouldn't have thought of!