Multiple Substitute in a Formula

[cart_alot]

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

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!

[Jeremy_Oglesby · Accepted solution]

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 one… at … a … time.

[cart_alot]

awesome thank you for writing out an example!

[Kai_Dickas]

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! :slightly_smiling_face:

Kai

[Jeremy_Oglesby]

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().

[Kai_Dickas]

Hi Jeremy,

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

Have a nice week.

Kai