Regex to divide a cell in to multiple cells based on number of char. and new/empty lines

[IMSL]

Hi Alteryx community,

I need a Regex formula to create multiple columns from a text and a column with "/"  before each new columns.

For example like below picture: creating multiple columns from MAIN_TEXT

Criteria:

1. each new column should not be more than 11 characters.

2. if the MAIN_TEXT has a line break, even if the total characters in the line are less than 11 characters, I need a new column (like column C) 

3. if there is a line break in MAIN_TEXT, I need a blank column (like column E)

4. if a line in the MAIN_TEXT is more than 11 characters, it should be broken to multiple columns. Each one max 11 characters. and NOT breaking a word in to two parts (Like columns G & I)

5. in total there can be up to 100 new columns

My solution:

step1: I removed all the line breaks from MAIN_TEXT with a formula: REGEX_Replace([MAIN_TEXT], '\n', ' ')

step2: I used this Regex: (.{1,11}\s)

step3: I used a Data Cleansing tool to Remove Null Columns

step3: I used multifield formula to add the Tag (I mean "/" ) columns.

But I lose existing line breaks and also empty lines in the MAIN_TEXT. It also cannot handle if there is more than 11 characters in a one line

Could anyone help me with this?

[binu_acs]

@IMSL Can you upload the input file?

[IMSL]

@binu_acs Here is the sample Excel file.

Thank you in advance :)

[Christina_H]

Here's a possible solution, using a formula from this thread to split items of more than 11 characters: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Interactive-Chart-Adding-text-wrap-in-...

I think it only splits it in two though, so it might not work for lines of 20+ characters.  You could get around that with an iterative macro to keep splitting it down.