Multiple Substitute in a Formula

greeting - airtable noob here with my first post :slight_smile:

i need to take a text string from a field called NAME - make it lower case - and then do search and replace for a number of characters . I have it working for one condition - replacing spaces with dashes.
SUBSTITUTE(LOWER(NAME), " ", “-”)

now i need to find special characters like ( ) " etc and eliminate from the string. is there a way to nest multiple commands…? or…? thanks!

1 Like

You can nest multiple SUBSTITUTE() commands, yes:

SUBSTITUTE(
   SUBSTITUTE(
      LOWER({Name}),
      " ",
      "-"
   ),
   "(",
   ""
)

This, for example, will first perform your substitution of dashes in for spaces, then on the resulting string that comes out of that, will replace any open parenthesis with nothing (ie, will delete them). Unfortunately, you have to just keep nesting each new condition like this, for each new character you need to replace. There is not a way (at least that I have discovered) to use conditional language within a SUBSTITUTE() statement, such as Replace "(" OR ")" with "". It’s all oneatatime.

4 Likes

awesome thank you for writing out an example!

2 Likes

Hi Jeremy,

thank you for the formular. I’m new to this so i’m not sure how to nest 3 substitutes commands in a formular. If you are able to post an example that would help me A LOT! :slight_smile:

Kai

1 Like
SUBSTITUTE(
  SUBSTITUTE(
    SUBSTITUTE(
      LOWER({Name}),
        " ",
        "-"
     ),
     "(",
     ""
   ),
   ")",
   ""
)

There’s three nested substitutes. You just have to make sure that the entire SUBSTITUTE(..., ..., ...) for a nested formula is in the first parameter space (before the first comma) of the SUBSTITUTE(1st, 2nd, 3rd) in which it is nested.

These evaluate from the inside out, so keep that in mind while nesting these. If, in an inner SUBSTITUTE(), you replace all “(” with “-”, and then in an outer SUBSTITUTE() you replace all “-” with “a”, you will end up replacing all the “-” that you put in through the inner SUBSTITUTE().

2 Likes

Hi Jeremy,

thanks for your quick answer. It helped me to get a better understanding how it works!

Have a nice week.

Kai

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.