Using SUBSTITUTE with IF

Emma Picardi

Hello! I'm working on creating an order form to automate a custom signage fabrication process. The tricky piece is taking all the "raw data" from the form responses and turning it into something easily legible for our manufacturer. Right now, I'm trying to use the SUBSTITUTE function to combine two text entries into one cell, but I only want the substitution formula to run when a specific type of sign is selected. Is it possible to use SUBSTITUTE and IF in the same formula? And if so, what does the syntax look like?

My current column formula looks like this: =SUBSTITUTE([Arrow Direction]@row, "__", [Cabin Direction Name]@row) + IF([Sign Type]@row = "cabin direction sign", ", " + Sides@row)

You can see in the second screenshot (highlighted in yellow) where the column formula has input an arrow symbol in the "Cabin Direction Copy" column for Row 5. What I want it to do instead, however, is only run the [Arrow Direction] substitution when [Sign Type] = "Cabin direction sign" (whereas in Row 5, [Sign Type] = "Neighborhood sign"). Is this possible?

That said, there could be a much simpler, more efficient way to arrive at the "FINAL Sign Copy" column, so I'm definitely open to other ideas! Thanks so much!

Jeff Reisman

The IF function will run whatever formula you give it as long as the positive condition is true. Try this:

=IF(([Sign Type]@row = "Cabin direction sign", (SUBSTITUTE([Arrow Direction]@row, "__", [Cabin Direction Name]@row) + ", " + Sides@row), "")

English: IF the Sign Type is "Cabin direction sign" then run this SUBSTITUTE formula plus a comma plus the value from the Sides column; otherwise, leave this cell blank.

Jeff Reisman

@Emma Picardi The logic with IF works like this:

=IF( logical statement, value if true, value if false )

When you start nesting them:

=IF( logical statement, IF( logical statement, value if true, IF (logical statement, value if true, value if false )))

You can also use AND or OR with the logical statements to check for more than one condition:

=IF(AND(ColumnA@row = 1, ColumnB@row =2), "Correct!", "Wrong!") (both column values must be true for the answer to be "Correct!")

=IF(OR(ColumnA@row = 1, ColumnB@row =2), "Correct!", "Wrong!") (just one of the column values must be true for the answer to be "Correct!")

Now, in your case, let's see if we have the logic correct:

IF the [Sign Type]@row = "neighborhood sign", and the [Arrow Direction]@row is a text value, then you want

 SUBSTITUTE([Arrow Direction]@row, "__", [Neighborhood Name]@row) + ", " + Sides@row

But if the [Sign Type]@row = "neighborhood sign", and the [Arrow Direction]@row is BLANK, then you only want

[Neighborhood Name]@row + ", " + Sides@row

If that's right, then let's try giving it two conditions to check for, with a value for if they're both true, and if they are not both true, we'll give it two other conditions to check for and a value if those are both true, and lastly, if neither set of conditions is true, tell it to leave the cell blank:

=IF(AND([Sign Type]@row = "neighborhood sign", ISTEXT([Arrow Direction]@row)), SUBSTITUTE([Arrow Direction]@row, "__", [Neighborhood Name]@row) + ", " + Sides@row, IF(AND([Sign Type]@row = "neighborhood sign", ISBLANK([Arrow Direction]@row)), [Neighborhood Name]@row + ", " + Sides@row, ""))

(As always, make sure the first open parentheses and the last close parentheses are the same color!)

Jeff Reisman

The IF function will run whatever formula you give it as long as the positive condition is true. Try this:

=IF(([Sign Type]@row = "Cabin direction sign", (SUBSTITUTE([Arrow Direction]@row, "__", [Cabin Direction Name]@row) + ", " + Sides@row), "")

English: IF the Sign Type is "Cabin direction sign" then run this SUBSTITUTE formula plus a comma plus the value from the Sides column; otherwise, leave this cell blank.

Emma Picardi

Oh it really was that easy! Thanks so much Jeff

Jeff Reisman

https://community.smartsheet.com/discussion/comment/325866#Comment_325866

Happy to help!

Emma Picardi

Hi again, one more for you while we're at it:

I'm trying to add one more layer to this where the column formula makes the same substitution for me IF the [Arrow Direction] column is not blank, but if it is blank, I still want it to copy the name (in this case, "Neighborhood" name rather than "Cabin") over into the cell with the [Sides] notation.

This formula makes the correct substitution (see Row 1 with text in red below):

=IF([Sign Type]@row = "neighborhood sign", SUBSTITUTE([Arrow Direction]@row, "__", [Neighborhood Name]@row) + IF([Sign Type]@row = "neighborhood sign", ", " + Sides@row))

And this formula correctly copies the [Neighborhood Name] into the column when [Arrow Direction] is blank (see Row 2 with text in blue below):

=IF([Arrow Direction]@row = "", [Neighborhood Name]@row) + IF([Sign Type]@row = "neighborhood sign", ", " + Sides@row)

And here's what it looks like when I (try to) combine them, (see Row 3 with text in yellow and Row 4 below):

=IF([Sign Type]@row = "neighborhood sign", SUBSTITUTE([Arrow Direction]@row, "__", [Neighborhood Name]@row), IF([Arrow Direction]@row = "", [Neighborhood Name]@row) + IF([Sign Type]@row = "neighborhood sign", ", " + Sides@row))

When I use this formula with [Arrow Direction] filled out, it removes the [Sides] notation at the end (Row 3), and when I use it with [Arrow Direction] blank, the [Neighborhood Copy] cell returns blank (Row 4).

I'm hoping there's just something off with the syntax...

Jeff Reisman

@Emma Picardi The logic with IF works like this:

=IF( logical statement, value if true, value if false )

When you start nesting them:

=IF( logical statement, IF( logical statement, value if true, IF (logical statement, value if true, value if false )))

You can also use AND or OR with the logical statements to check for more than one condition:

=IF(AND(ColumnA@row = 1, ColumnB@row =2), "Correct!", "Wrong!") (both column values must be true for the answer to be "Correct!")

=IF(OR(ColumnA@row = 1, ColumnB@row =2), "Correct!", "Wrong!") (just one of the column values must be true for the answer to be "Correct!")

Now, in your case, let's see if we have the logic correct:

IF the [Sign Type]@row = "neighborhood sign", and the [Arrow Direction]@row is a text value, then you want

 SUBSTITUTE([Arrow Direction]@row, "__", [Neighborhood Name]@row) + ", " + Sides@row

But if the [Sign Type]@row = "neighborhood sign", and the [Arrow Direction]@row is BLANK, then you only want

[Neighborhood Name]@row + ", " + Sides@row

If that's right, then let's try giving it two conditions to check for, with a value for if they're both true, and if they are not both true, we'll give it two other conditions to check for and a value if those are both true, and lastly, if neither set of conditions is true, tell it to leave the cell blank:

=IF(AND([Sign Type]@row = "neighborhood sign", ISTEXT([Arrow Direction]@row)), SUBSTITUTE([Arrow Direction]@row, "__", [Neighborhood Name]@row) + ", " + Sides@row, IF(AND([Sign Type]@row = "neighborhood sign", ISBLANK([Arrow Direction]@row)), [Neighborhood Name]@row + ", " + Sides@row, ""))

(As always, make sure the first open parentheses and the last close parentheses are the same color!)

Emma Picardi

Amazing! Thank you so much Jeff!