Split Text to Columns and Append as New Rows in Same Column and Add Formulas

[scotthenn]

Below is the input and output I am trying to achieve by what I believe will be a combination of text-to-column, union, and summarize by (missing some specifics along each step). Wanted to break down the problem as follows. Appreciate any support in advance.

Question 1a - I am trying to split the text of a column with a dynamic delimiter - as seen below sometimes there are commas + spaces and sometimes only a comma. How can I address this distinction? Solved with string formulas to clean up first then text to column

Question 1b - How many columns do I select if the amount of "entities" (i.e. the amount of new columns that may be required) is variable?(I can just pick the most there could be, but curious if there is a dynamic option - the number of columns is relevant for question 3 below) Solved - just check the box "Split to Rows"

Question 2 - How can I append those new columns back into the original "entity" column as new rows with duplicate information? CHeck the box "Split to Rows" 

Question 3 - How can I create a formula that can divide the total balance by code over the number of entities? (e.g. if there are 3 entities in a code then divide balance by 3?) Now that I have rows this shouldn't be hard to figure out : )

[DanHare · Accepted solution]

Hey Scott

Quick tip - It would have been easier if you had included the original workflow with textinput, then we could just change it.

I don't have time to reproduce, have to go to work !

However, in words since you now have columns...

In your Text to Columns retain Code

Summarize 1 ;

- Group by Code and Balance

- Count Entities

Join

Row based data to Summarize 1 by code ;

Formula - Divide Balance from summary by Count Entities

Select - Drop spurious columns