String Replace

[ManuelRodrigues]

Hi Team,

I am looking to replace a particular word in a string "Home" [ Start Location] with the results in [City] and if it does not contain "home" leave it as is however i am struggling with wrapping this in a if statement with find and replace string formulae?

[binu_acs]

@ManuelRodrigues 

IIF(StartsWith([Start Location], 'Home'),Replace([Start Location], 'Home', [City]),[Start Location])

[Thableaus]

Hey @ManuelRodrigues 

Something like this should work

IF Contains([Start Location], "HOME") THEN Replace([Start Location], "HOME", [City])

ELSE [Start Location] ENDIF

Cheers,

[ManuelRodrigues]

thanks @Thableaus @binu_acs some of the strings have home in the middle of the string or end. sorry should have mentioned before. Would this still work?

[Thableaus · Accepted solution]

@ManuelRodrigues 

If you want to avoid surprises, you could use REGEX Match function to identify word boundaries and just replace the full word "HOME", and not pieces of it inside other words.

Example: if a word contains "HOME" like homeless, homeboy, homework, the string shouldn't be replaced, right?

So you would use word boundaries (\b)

IF REGEX_Match([Field1], ".*\bHOME\b.*") THEN Replace([Field1], "HOME", [Field2]) ELSE [Field1] ENDIF

Cheers,

[SoccerTil]

Don't forget the short version fuction for If ...Then ... Else ... EndIf 

IIF (
Contains([Start Location], "HOME")
, Replace([Start Location], "HOME", [City])
, [Start Location]
)

[ManuelRodrigues]

@Thableaus @binu_acs  thanks for your help and all that have also helped!