Remove extra spaces within a string?

[RPA]

Hi! Is there a formula (or other solution) to remove extra whitespace within a string? My formula right now looks like: 

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM({name}),"  "," ")," :",":"),” .”,”.”),” ?”,”?”),” ,”,”,”)

This works great to remove whitespace before/after the string using Trim and then remove double spaces and spaces before punctuation using Substitute. I'm wondering if there's a way to also remove instances of 3 or more spaces within a string without separately writing each possibility (3 spaces, 4 spaces, etc) as substitutes.

Thanks for any help!

[Jeff_Haskin · Accepted solution]

Here's a formula that will both remove spaces before punctuation AND correct multiple consecutive spaces all in one concise formula:

REGEX_REPLACE(
  REGEX_REPLACE(
    TRIM({name}),
    "\s+(\,|\;|\.|\-|\?|\:)",
    "$1"
  ),
  "\s+",
  " "
)

This first trims the "name" field, then removes spaces before punctuation, then replaces any remaining instances of multiple consecutive spaces with a single space.

You can also change which punctuation it's looking for by simply adding or removing the desired punctuation from the list.

Enjoy!

[RPA]

Thank you! That works perfectly! Very much appreciated!