Replace a word and output with original sentence value

[junaid2ali]

I have a column {Sentence with Plain Value}, whenever a value that is listed in column {Plain Value} exists, I want it replaced with the value in column {Value with R Ball} and outputted with other initial values in the sentence.

For example:

I love to have a Big Breakfast will be -> I love to have a Big Breakfast®

A Burger is delicious -> A Burger® is delicious

How can I accomplish this?

[Ben_Young1]

Hey @junaid2ali! 

You don't actually need the Value with R Ball field at all to do this.
Create a new formula field and give this formula a shot: 

IF(
    AND(
        {Plain Value}, {Sentence with Plain Value}
    ),
    IF(
        FIND(
            {Plain Value}, {Sentence with Plain Value}
        ),
        SUBSTITUTE(
            {Sentence with Plain Value}, {Plain Value}, {Plain Value} & "®"
        ),
        {Sentence with Plain Value}
    ),
    {Sentence with Plain Value}
)

You'll want to double check that all the field names and values are correct if it throws you an error.

[AirOps]

Hi @junaid2ali, 

You can also accomplish this by using the SUBSTITUTE Formula. Try this: 

SUBSTITUTE({Sentence With Plain Value},{Plain Value}, {Value with R ball})

Some more information about the SUBSTITUTE formula (curtesy of Airtable):

SUBSTITUTE(string, old_text, new_text, [index])

Replaces occurrences of old_text with new_text. You can optionally specify an index number (starting from 1) to replace just a specific occurrence of old_text. If no index number is specified, then all occurrences of old_text will be replaced

I hope this helps!

[junaid2ali]

Thanks for this, but this solution assumes the R ball is always at the end correct? which is not the case.

[Ben_Young1]

IF(
    AND(
        {Plain Value}, {Sentence with Plain Value}, {Value With R Ball}
    ),
    IF(
        FIND(
            {Plain Value}, {Sentence with Plain Value}
        ),
        SUBSTITUTE(
            {Sentence with Plain Value}, {Plain Value}, {Value With R Ball}
        ),
        {Sentence with Plain Value}
    ),
    {Sentence with Plain Value}
)