Replacing text with other text

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?

Output

Accepted answers

afv2688

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

All comments

CarlDi

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

afv2688

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

lookupReplacement.yxmd

EricaDakin

That one works, thanks very much!

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167