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?

dkchandana3
7 - 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

7 REPLIES 7
tlarsen7572
11 - Bolide

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
ACE Emeritus
ACE Emeritus
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
19 - Altair
19 - Altair
Ben,

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

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
dkchandana3
7 - Meteor

Used a similar one.

 

Thank You

dkchandana3
7 - Meteor

Thank You!

BogdanCalacean
7 - Meteor

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
11 - Bolide

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