Replacing text with other text

[EricaDakin]

I have a problem with replacing some text in my file. My data has a Religion field, where some values are duplicates but with different text. Specifically I have these three options:

Other

Other Religion or Belief

Any Other Religion or Belief

I want to replace the first two with the last, but because that value includes the first two, I cannot simply enter a Replace formula where I replace "Other" and "Other Religion or Belief" with "Any Other Religion or Belief", because I end up with a field that says Any Any Any Other Religion or Belief Religion or Belief".

Any ideas on how I fix this?

[CarlDi]

hey @EricaDakin - a lookup table utilizing the Find & Replace tool maybe the best option here.

[afv2688 · Accepted solution]

Hello @EricaDakin ,

Another option would be to use the formula tool:

IF [Religion] = "Other" OR "Other Religion or Belief"

THEN "Any Other Religion or Belief"

ELSE [Religion]

ENDIF

Cheers

[JohnJPS]

Hi @EricaDakin,

If the list of replacements is short, you can use a switch statement...

Switch([f1],[f1],
"Other","Any Other Religion Or Belief",
"Other Religion Or Belief","Any Other Religion Or Belief")

... otherwise a lookup table can work; just make sure, in this example, to match against the entire string; (see attachment).

Hope that helps!

John

[EricaDakin]

That one works, thanks very much!