Help

Multiple Substitute in a Formula

Solved
Jump to Solution
6349 5
cancel
Showing results for 
Search instead for 
Did you mean: 
cart_alot
4 - Data Explorer
4 - Data Explorer

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!

1 Solution

Accepted Solutions
Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

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.

See Solution in Thread

5 Replies 5
Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

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.

awesome thank you for writing out an example!

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

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
6 - Interface Innovator
6 - Interface Innovator

Hi Jeremy,

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

Have a nice week.

Kai