Multiple Substitute in a Formula


#1

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!


#2

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.


#3

awesome thank you for writing out an example!