Split column to multiple columns based on cell format (Excel)

[Koen_Stam]

Dear Alteryx,

I have a question related to data preparation. My excel file consist of project revenue data, grouped by customers and country.

The first column states the customer name, country name or project number.

Format wise, the country name has space between the start of the characters and the left cell border (1 time 'increase indent').

The project number has double the space between the start of the characters and the left cell border (2 times 'increase indent').

The customer name has no space between the start of the characters and the left cell border.

I would like to split the first column into 3 columns, 1 for company name, 1 for country name, 1 for project number.

Somehow Alteryx therefore needs to be able to read the cell format in order to identify the 3 different types (content starting directly or after 1 or 2 times space). 

How can I do this with Alteryx / Excel?

Please find attached an example of how my data looks like.

Kind regards,

Koen

[pcatterson · Accepted solution]

Is this what you mean?

[Koen_Stam]

Unfortunately, I cannot open your file. I get an error message that you have made your workflow in a newer version of Alteryx. Could you save the workflow in an older version of Alteryx or tell me how I can still open your file?

[pcatterson]

If you open the workflow in Notepad, you can change the version of the workflow back to the version you are using.  It is on the second line of the code.

Best

Paul

[Koen_Stam]

Thanks, this is perfect! The workflow works fine, this is exactly what I was looking for.

[Koen_Stam]

Hmm actually it was not exactly what i was looking for. 

The difference is column A. 

In my example, Country name starts after 1 tab (not space from the spacebar) and Project after 2 tabs (in excel is called 'increase indent'). 

In your example, you have used the spacebar to create space, which is different from my problem.

Do you have an answer to my problem?

Kind regards,

Koen