Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
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
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
20 - Arcturus
20 - Arcturus
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
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