Create a Table from Values within a Cell

[gfisch13]

Hi Folks, I've been on a bit of a hiatus from using Alteryx and have finally received approval to use the tool in my new role, but I'm a bit rusty.

I'm trying to create a table from the values contained in each cell in the attached file.   Each cell can have one name or as many as 10, I haven't exceeded that value.   I'm trying to extract the names from each cell and create a new table to use in my analysis but I'm not certain how to approach this task and would appreciate your input.  

Sample file is attached.   Thanks, George

[abacon · Accepted solution]

@gfisch13 If you are trying to get each name into it's own line so it can be used as a table, use the Text to Columns tool - Split to Rows on the Comma. Then you can use a data cleansing tool to clean up the whitespace. Does this get the desired output?

Bacon

[gfisch13 · Accepted solution]

@abacon, This worked perfectly.  In my regular dataset, these values are typed in so I knew there were also variations but this method allowed me to identify them and now I can use this table in all my workflows.

An aside question, the source of this data is Excel, and certain names are colored red to stand out.  Is there any way that Alteryx can identify that quality and carry it through, I'm thinking no, but thought I'd ask.

[abacon]

@gfisch13 I don't believe that is possible to read in the records font color from excel. But, I could be wrong on that.

Bacon

[davidskaife]

Hi @gfisch13 @abacon 

So you can read in the font colour of a record from Excel, but you wouldn't be able to maintain that colour within Alteryx itself. Building on the back of this excellent thread on reading in highlighted cells from excel https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Validating-and-filtering-excel... i've had a play around with identifying font colours.

Take this example input sheet, with the words Hello and Task highlighted different colours:

If we import the Excel file as a ZIP, and look in the xl/sharedStrings.xml record we can parse out the XML to identify the contents, and details, of each cell:

Within the breakdown of the OuterXML you get a record for each word (where they differ, so for example in the first row 'Bob, Task' are the same so they get grouped together, whereas for the second row we get three records as 'Bob,' is different to 'Task,', which is different to 'Helo' 

Within the individual record you get the following information, which includes the RGB Hex code for the highlighted word:

I've not taken it any further than this, but thought it worth sharing. Ultimately it depends on what you want to do with the identified colour - as mentioned you cannot retain the coloured word within Alteryx itself but it opens up options to identifying words that have been highlighted in a different colour, or even potentially the ability to carry that formatting over to the output somehow....

I've attached my workflow

[abacon]

@davidskaife I was going to say it might be possible to read in the underlying code to get it but would have been out of my league on that one. That's awesome, thank you for clarifying.

Bacon

[gfisch13]

This is amazing, I'm going to give it a try and let you know the outcome.  Appreciate the input.

George