Find and Replace Using 2 Fields

[bertal34]

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!

[vizAlter · Accepted solution]

@bertal34 — Try attached the workflow:

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

[MarqueeCrew · Accepted solution]

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

Cheers,

Mark

[mceleavey · Accepted solution]

Hi @bertal34 ,

I've packaged this into a macro for you:

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.

Hope this helps.

M.

[bertal34]

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

[vizAlter]

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

@MarqueeCrew — Without using any Formula expression.... really interesting & cool solution!

@mceleavey — I liked your solution too!

@bertal34 — Thank you!

[bertal34]

Thank you all for your assistance.  Greatly appreciated!!