Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Remove extra spaces within a string?

Topic Labels: Formulas
Solved
Jump to Solution
2374 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!