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!

[srea541]

Hi Jonathan,

That solves the full stop problem!

Apologies, I didn't make clear how I wanted the output to look!

The columns that are read in earlier:

Should come out at the end like:

So that every response, for example, in q2a_9_other, is listed underneath each other (as they are separate responses from different people)

But it currently jams all of them together:

Many thanks,

Stephen

[Jonathan-Sherman · Accepted solution]

I see what you're aiming for now @srea541. This should do the trick?

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]

If you want to convert the empty cells to null then simply add a multi field formula on the end:

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,

Amazing, this works just as it is needed to!

Thank you for your patience this morning and for all your help in getting this up and running! 🙂

Stephen

[srea541]

Hi Jonathan,

One more thing, some words with apostrophes and a letter at the end are appearing like "It' s", "Can' t" - with a space between the apostrophe and the S.

This is only happening with apostrophes that were already there i.e. the wordbank didn't need to add them

Would you know how to change this?

Many thanks,

Stephen