Remove extra spaces within a string?

Question

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 Answer

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!

Thank you! That works perfectly! Very much appreciated!

RPA · Answer

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!

Thank you! That works perfectly! Very much appreciated!

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded