using REGEX to loop through a spreadsheet?

[srea541]

I'm new to Alteryx and I'm trying to finish off a workflow which will replace misspelled words using a Excel sheet:

This table is joined to another table to look like this:

The formula I am currently using is:

REGEX_Replace([_CurrentField_],[Wrong Spelling],[Right Spelling])

However, this formula only replaces all instances of the word 'cant' in row one, and does not run through all the rows. I'm not sure where to go from here. 

This is what I get:

'Im' needs to be replaced as well but is not.

Any help would be greatly appreciated!

[Jonathan-Sherman]

Hi @srea541,

Rather than using regex expressions to achieve this could you use a simple find and replace tool? I'll put together a sample workbook for you.

If this solves your issue please mark the answer as correct, if not let me know!

Jonathan

[Jonathan-Sherman]

Hi @srea541,

So as i said you could use a find and replace tool to look up misspelled words and replace with the correct spelling:

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[srea541]

Hi Jonathan,

I have a solution that uses find and replace:

The problem is I have to find and replace the words in each column individually, which works but is very time consuming. For the project I'm working on, I would need to do this 24 times to achieve what I need.

Stephen

[Jonathan-Sherman]

Hi @srea541,

So in this case i would transpose your data before the find and replace tool, i'll mock up a sample.

If this solves your issue please mark the answer as correct, if not let me know!

Regards,

Jonathan

[Jonathan-Sherman]

Hi @srea541,

Please see the attached:

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[Jonathan-Sherman]

However @srea541 if you want to be 100% accurate i would go down the route of tokenising each word and then using the find and replace technique. This will stop any issues of finding a word in a word, for example "estimate" you would find "im" and become "estI'mate".

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[Jonathan-Sherman]

And finally using the field info and dynamic rename tools to ensure the column headers exactly the same as before the crosstab:

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[srea541]

Hi Jonathan,

I think we're getting closer!

The issue is now on the last step, it is sticking all of them into one row:

Another thing I noticed was that the find and replace function removes the '.' from the end of the fields as well as any occurences in the middle of the string. These would need to stay in.

Stephen

[Jonathan-Sherman]

Hi Stephen,

In that case how about this:

I'm not sure how you want the output structured? As you've said you don't want it all on one row?

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan