Skip to main content

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!

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!

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!

Reply


Terms & Conditions