Combine Text and Replace

Justin_Schafer · ‎Jan 03, 2018

I’m trying to combine the text from two columns into a third column then replace that text with new text.

For Example,

Column A = "Like"
Column B = "Live With"
Column C (combine column A & B to “Like + Live With” and change that text to “Performance”)

Column A = "Dislike"
Column B = "Don’t Care"
Column C (combine column A & B to “Dislike + Don’t Care” and change that text to “Attractive”)

Right now I’m trying to use the CONCATENATE and SUBSTITUTE commands. Can anyone show me an example of the formula?

Andrew_Johnson1 · ‎Jan 03, 2018

Its not pretty clear as to what you are wanting…

w.r.t to your first example
Do you want A & B to be replaced with “Performance” and C with “Like Live With” ?

Justin_Schafer · ‎Jan 03, 2018

Depending on the answer combination from the positive question and negative question columns will result in one of size priorities.

I don’t really need the “Result” column, It was just the way I was trying to display the correct Priority.

In Excel it’s essential a small table (see image here - https://cl.ly/1A0O2N0m2n0I

W_Vann_Hall · ‎Jan 03, 2018

You need a nested IF() statement:

IF(
····{Negative Question}='Like',
····IF(
········{Positive Question}='Like',
········'Questionable (Invalid)',
········'Reversal (Invalid)'
········),
····IF(
········OR(
············{Negative Question}='Expect It',
············{Negative Question}='Don\'t Care',
············{Negative Question}='Live With'
············),
········IF(
············{Positive Question}='Like',
············'Attractive',
············IF(
················{Positive Question}='Dislike',
················'Reversal (Invalid)',
················'Indifferent'
················)
············),
········IF(
············{Positive Question}='Like',
············'Performance',
············IF(
················{Positive Question}='Dislike',
················'Questionable (Invalid)',
················'Must-Be'
················)
············)
········)
····)

Note: I’ve used the Middle Dot character (’·’, or 0xB7) to replace spaces in the above formula to hold indentation, so the formula would be halfway legible. You can either search and replace ‘·’ with ’ ’ and then paste the newly spaced-out formula into your field, or you can copy and paste the one from the configuration for the {Priority} field in this demo base.

W_Vann_Hall · ‎May 17, 2018

Having since learned how to do properly indented code in Markdown, I thought I’d fix this.

IF(
    {Negative Question}='Like',
    IF(
        {Positive Question}='Like',
        'Questionable (Invalid)',
        'Reversal (Invalid)'
        ),
    IF(
        OR(
            {Negative Question}='Expect It',
            {Negative Question}='Don\'t Care',
            {Negative Question}='Live With'
            ),
        IF(
            {Positive Question}='Like',
            'Attractive',
            IF(
                {Positive Question}='Dislike',
                'Reversal (Invalid)',
                'Indifferent'
                )
            ),
        IF(
            {Positive Question}='Like',
            'Performance',
            IF(
                {Positive Question}='Dislike',
                'Questionable (Invalid)',
                'Must-Be'
                )
            )
        )
    )