community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

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

Meteor

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

Highlighted

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.

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

Ben
Alteryx Certified Partner
Alteryx Certified Partner
Ben,

Under the covers, that is the formula being used.
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Meteor

Used a similar one.

 

Thank You

Meteor

Thank You!

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?

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.

Labels