Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
The Expert Exam is now live online! Read about the specifics and what it took to bring it to life in the blog by our very own Elizabeth Bonnell!
SOLVED

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

Highlighted
6 - Meteoroid

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

 

 

Highlighted
11 - Bolide

Is this what you mean?

Highlighted
6 - Meteoroid

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?

11 - Bolide

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.

 

VersionChange.png

 

Best

Paul

Highlighted
6 - Meteoroid

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

Highlighted
6 - Meteoroid

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

Labels