Combine Text and Replace


#1

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?


#2

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” ?


#3

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


#4

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.


#5

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'
                )
            )
        )
    )