Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Feb 17, 2023 05:32 PM - edited Feb 17, 2023 05:32 PM
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!
Solved! Go to Solution.
Feb 17, 2023 05:59 PM - edited Feb 17, 2023 06:01 PM
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!
Feb 17, 2023 05:59 PM - edited Feb 17, 2023 06:01 PM
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!
Feb 17, 2023 07:09 PM
Thank you! That works perfectly! Very much appreciated!