Trim, but leaving a single space between words

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:

Record	Actual Data	Desired Data
1	AB 234	AB 234
2	CD 123	CD 123
3	EGSC 123 31	EGSC 123 31

Regex

Preparation

Accepted answers

rohanonline

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

All comments

rohanonline

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

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

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.

Keep one space.yxmd

rohanonline

Thanks @gnans19

TomAdams

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

uips

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?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703