How to replace two or more white spaces with a single white space?

[dkchandana3]

Here is the situation,

There are two white spaces before '-' in the first line where as there is only one white space in the second line. How do I get rid of two white spaces in the first line?

2018 Small North Chart  - GRE

2018 Small South Chart - GRY 

Thank You

[tlarsen7572 · Accepted solution]

Use this in a formula tool:

REGEX_Replace([Field], "\s+", " ")

The \s+ pattern matches one or more whitespace characters, which all get replaced by a single space.

[BenMoss]

I'm going to go simpler than that; check out the data cleanse tool and there is an option to trim duplicate whitespace!

Ben

[MarqueeCrew]

Ben,

Under the covers, that is the formula being used.

[dkchandana3]

Used a similar one.

Thank You

[dkchandana3]

Thank You!

[BogdanCălăcean]

Great answer. What if there was "A&B 5 000,01" and would like to see "A&B 5000,01"? How would you do that?

[tlarsen7572]

I like to start with the simplest regex solution possible, and adjust as I hit edge cases.  For your situation, I would try looking for a number, followed by a space, followed by a number.  Then replace it with the 2 numbers.  The regex function which does that looks like this:

REGEX_Replace([Field], "(\d) (\d)", "$1$2")

You will have to check your data a bit to make sure no unwanted side-effects occur and that all of the changes you are expecting are made.  If it accomplishes the goal, great!  If not, we can tweak further.