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

Trim, but leaving a single space between words

TomAdams
5 - Atom

Hi all,

 

I've got a set of data I'm trying to match to another set of data on a common field.  The required field in one set, however, has a random number of spaces between some words, which means it won't match the other set.

 

For various reasons, I'm not able to trim both sets and of data and match the trimmed versions.  So I need to find a way of trimming the set with random spaces in such a way that it leaves just a single space between each word - which will match to the other set.  I suspect that there's a relatively simple RegEx answer, but I just can't get it to work.

 

Help?

 

Here's a bit of dummy data to show what I'm aiming for:

 

RecordActual DataDesired Data
1AB      234AB 234
2CD   123CD 123
3EGSC    123    31EGSC 123 31
6 REPLIES 6
rohanonline
10 - Fireball

You can use "Data Cleansing" function and select "Leading and Trailing Whitespaces" along with "Tabs, Line Breaks and Duplicate Whitespaces" options at the least

Kenda
16 - Nebula
16 - Nebula

If you did want to use RegEx, you still could. Add a Formula tool and use the following two statements on your Actual Data field: 

REGEX_Replace([Actual Data], "\s+"," ")

Trim([Actual Data])

 

Together, these should leave one space between words and no leading or trailing white space.

gnans19
11 - Bolide

Combination of texttocolumn, transpose, filter and summarize(concat with single space) would also do the magic. Just another way to solve the same problem. But @rohanonline suggesttion is the easy and simple fix. No need to reinvent the wheel again.

 

space.png

rohanonline
10 - Fireball

Thanks @gnans19

TomAdams
5 - Atom

Thanks for all the suggestions - data cleansing is clearly the easiest, but the others were interesting approaches to the same problem.

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?

Labels