Alteryx Designer Discussions

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

Creating a workflow that checks a spreadsheet for strikethrough characters

Arothman12
7 - Meteor

Hi Alteryx community,

 

I am doing some investigating whether it is possible to leverage alteryx to search a workflow that has merged and unmerged cells for characters that have been striked through as well has specific color text. I have not built a workflow so I apologize that I do not have an example to give physically. I can describe what I am looking for here in this post to the best of my ability and I am of course willing to respond to follow up questions.

 

The example I can give is as follows, let say for example this table below is a spreadsheet it contains cells that are merged and unmerged and within those cell is text that is stricken through or colored another color to get the users attention to either a word being added or changed. I want to build a workflow that will Identify if there are characters that are stricken through or color a different color other than black front.

So here is how the spreadsheet could obviously this is way dumbed down version. Any ideas on how to accomplish this would be greatly appreciated, even if it's just a starting point I will take any help I can get.

 

Thank you in advance.

 

-Avidan Rothman

 

These two cells are merged   example of a strikethrough These two cellsare merged for the example997  
       
  text example     
       
 This is colored to point out a grammar change texts here is another examples   
       
       
      

 

5 REPLIES 5
mceleavey
16 - Nebula

Hi @Arothman12 ,

 

Not that I'm aware. This is not the actual data but a visual, so you would need to pull in the stylesheets as you would with determining the colour of the cell.

You can read an article by @WilliamR  HERE on how to pull in colours, you will need to adapt the Python to pick up strikethrough characters rather than cell colouring, but the principle will be the same.

Obviously, the advise here is to capture the data correctly rather than using Excel visuals (or stop using Excel, obviously).

The article is in French but Google should convert it if your French is as bad as mine 🙂

 

Hope this helps,

 

M.

 

 

oly
Alteryx
Alteryx

Hey @Arothman12 , unfortunately most Excel drivers are color/font blind. I suggest you would make an easy "Cover Over sheet", that copies from the original only what is applicable. This article is pretty helpful: https://excelribbon.tips.net/T010780_Colors_in_an_IF_Function.html

 

Once you have that worksheet with only changes/amendments, you can load it up.... Automation-wise, again, we won't be able to export a template with the formulas, so you can go with Excel macros if copying a sheet is too difficult.

Arothman12
7 - Meteor

Thank you for the response if I have to do a way with checking for colors I can do so I am more concerned with finding strikethroughs is that something that can be done and how?

Arothman12
7 - Meteor

@mceleavey wrote:

Hi @Arothman12 ,

 

Not that I'm aware. This is not the actual data but a visual, so you would need to pull in the stylesheets as you would with determining the colour of the cell.

You can read an article by @WilliamR  HERE on how to pull in colours, you will need to adapt the Python to pick up strikethrough characters rather than cell colouring, but the principle will be the same.

Obviously, the advise here is to capture the data correctly rather than using Excel visuals (or stop using Excel, obviously).

The article is in French but Google should convert it if your French is as bad as mine 🙂

 

Hope this helps,

 

M.

 

 



@mceleavey wrote:

Hi @Arothman12 ,

 

Not that I'm aware. This is not the actual data but a visual, so you would need to pull in the stylesheets as you would with determining the colour of the cell.

You can read an article by @WilliamR  HERE on how to pull in colours, you will need to adapt the Python to pick up strikethrough characters rather than cell colouring, but the principle will be the same.

Obviously, the advise here is to capture the data correctly rather than using Excel visuals (or stop using Excel, obviously).

The article is in French but Google should convert it if your French is as bad as mine 🙂

 

Hope this helps,

 

M.

 

 


Thank you for the response if I have to do a way with checking for colors I can do so I am more concerned with finding strikethroughs is that something that can be done and how?

AZuc
Alteryx
Alteryx

@Arothman12 it is possible although tricky.

 

First take a look on that answer about colors (not using python). It will describe a little about internal xlsx structure https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Delete-columns-based-on-color/m-p/8427...

 

You can identify the strikes taking a look on  xl/SharedStrings.xml (at least for strikes that takes part of the cell)

 

Let me show you an example

AZuc_0-1636636236524.png

 

Let me try to find the definition for C2

Cell definition is in xl/worksheets/sheet1.xml

AZuc_1-1636636399777.png

 

"C2" is the cell and "50" points to xl/sharedStrings.xml, that stores all the strings in the workbook. Strings are stored by position, 50 means the 50th entry in the list.

 

going to xl/sharedStrings.xml, I see

AZuc_2-1636636537948.png

 

This is what happens when Excel stores a partial struck text. It's different when it strikes the whole cell (I believe there is a font definition for this case).

 

So, answering about "if it can be done", the answering is yes, and about "how" you need to go deeper in the excel structure.

André Zuccatti

Sales Engineer - LATAM -
Alteryx, Inc.


Labels