Find and replace or substitute

Desertbird · 12/27/22

Hello,

What is the best way to turn ###-###-#### into ########## ? Some entries have parentheses, dashes or spaces, but it needs to pull just the numbers.

I figured it would be either find and replace or substitute, but I'm not sure if I need an additional app to do those. Any help would be appreciated!

Andrée Starå · 12/27/22

Hi @Desertbird

I hope you're well and safe!

Try something like this.

You'd use the SUBSTITUTE function for each option in a string.

https://help.smartsheet.com/function/substitute

=SUBSTITUTE(SUBSTITUTE(your_cell@row, "-", ""), ".", "")

Did that work/help?

I hope that helps!

Have a fantastic week & Happy Holidays!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Darren Mullen · 12/27/22

@Desertbird Substitute would probably be the best as you could use it to replace all instances with an empty string "". I would say just use some nested substitute statements and search for parentheses, dashes, and spaces. That should do the trick.

The best solution if possible would be to do this cleanup on the entry of the data or enforce validation, but that may not be an option if you are just processing existing data.

I made a quick video to show how to use the Nested substitute functions:

https://youtu.be/TyfNg-c68-c

Andrée Starå · 12/27/22