Trim, but leaving a single space between words

[TomAdams]

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

[rohanonline · Accepted solution]

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.

[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.

[BogdanCalacean]

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?