Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Remove extra spaces within a string?

Topic Labels: Formulas
Solved
Jump to Solution
2992 2
cancel
Showing results for 
Search instead for 
Did you mean: 
RPA
5 - Automation Enthusiast
5 - Automation Enthusiast

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!

1 Solution

Accepted Solutions
Jeff_Haskin
6 - Interface Innovator
6 - Interface Innovator

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.

 

Jeff_Haskin_0-1676685529067.png

 

Enjoy!

See Solution in Thread

2 Replies 2
Jeff_Haskin
6 - Interface Innovator
6 - Interface Innovator

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.

 

Jeff_Haskin_0-1676685529067.png

 

Enjoy!

RPA
5 - Automation Enthusiast
5 - Automation Enthusiast

Thank you! That works perfectly! Very much appreciated!