Create a Table from Values within a Cell

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

AlteryxCellTableCreation.xlsx

Parse

Best Practices

Preparation

Accepted answers

abacon

@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

@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.

All comments

abacon

Bacon

gfisch13

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-data-based-on-color/td-p/1229587 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

Excel Colour Extract.yxzp

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703