community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

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

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

 

 

ACE Emeritus
ACE Emeritus

Is this what you mean?

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?

Highlighted
ACE Emeritus
ACE Emeritus

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

Meteoroid

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

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