Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Find and Replace Using 2 Fields

bertal34
9 - Comet

Please see attached example.  I'm trying to find the value in Field2 from Field1 and replace it with nothing.  The DesiredOutput is what I'm trying to achieve.  It must be case insensitive as well.

 

thank you in advance!

7 REPLIES 7
vizAlter
12 - Quasar

 

@bertal34 â€” Try attached the workflow:

 

REGEX_Replace([Field1], "\s"+[Field2]+"\b", "")

 

vizAlter_0-1600267699241.png

 

 

 

 

 

 

 

 

MarqueeCrew
20 - Arcturus
20 - Arcturus

@bertal34 ,

 

The following process parses each word and compares it to the second field value.  If the field 2 value appears once or more, it will be dropped.  The resulting words get concatenated back together.

 

capture.png

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
mceleavey
17 - Castor
17 - Castor

Hi @bertal34 ,

 

I've packaged this into a macro for you:

 

mceleavey_0-1600267869432.png

 

mceleavey_1-1600267882846.png

You simply need to group the fields in the macro by the original field, and on the "Questions" tab, map field one and the field containing the text you want to replace. Then it will run for all values.

 

mceleavey_2-1600267968104.png

 

 

Hope this helps.

 

M.

 



Bulien

bertal34
9 - Comet

@vizAlter  How would you alter to only replace the last word in Field1?

vizAlter
12 - Quasar

@bertal34 â€” Use below in Formula if you just want to remove the last string from your cell:

 

 

REGEX_Replace([Field1], "(.*.)(\<\w+\>$)", "$1")

 

 

Then, it does not need [Field2]

And, you may treat this RegEx function with IF..ELSE..ENDIF to manipulate accordingly, let me know if this answers your query.

 

And, if you just want to fetch the last word, then you may also use the inbuilt functions, see below example for your reference:

 

ReverseString(GetWord(ReverseString([Field1]),0))

 

then function below will give you the same result (it will remove the last word):

Trim(Left([Field1], Length([Field1])-Length([GetLastWord])))

 

(I have created a couple of examples in enclosed workflow, if you want)

 

 

If it resolves your query please mark it "Solved" or "Solved" with a Like. This will help other users find the same answer/resolution.  Thank you.

vizAlter
12 - Quasar

@MarqueeCrew â€” Without using any Formula expression.... really interesting & cool solution!

@mceleavey â€” I liked your solution too!

@bertal34 â€” Thank you!

bertal34
9 - Comet

Thank you all for your assistance.  Greatly appreciated!!

Labels
Top Solution Authors