Hi all, I have bit cluttered data set with spacing issue in it like mentioned scenarios below in value1 column UserIDValue1134562British Columbia ,Canada134562Canada,British Columbia3547Canada, British Columbia45769 Canada,British Columbia How to remove unwanted spacing between text and how to align all rows data in one format as it should be? Thank you

Data Cleansing Query

Hi all,

I have bit cluttered data set with spacing issue in it like mentioned scenarios below in value1 column

UserID	Value1
134562	British Columbia ,Canada
134562	Canada,British Columbia
3547	Canada, British Columbia
45769	Canada,British Columbia

How to remove unwanted spacing between text and how to align all rows data in one format as it should be?

Thank you

Workflow

Datasets

Parse

Data Investigation

Accepted answers

kushalb

I got the solution Shankar when i use Data cleansing and in there remove all whitespaces

it worked.

All comments

ShankerV

@kushalb

Can you please share the expected output, so it is easy to understand.

How to remove unwanted spacing between text and how to align all rows data in one format as it should be?

The highlighted is little confusing requirement.

Many thanks

Shanker V

ShankerV

@kushalb

Seeking more clarity which you consider as unwanted space.

Space between , British

or space between British Columbia

or space between British Columbia ,

ShankerV

@kushalb

I assume you are expecting the below output.

Value1

British Columbia,Canada

Canada,British Columbia

1. Use the Input tool

2. Use the Regex tool

Column to Parse - Value1

Regular expression - (.*),(.*)

Output method - Parse

3. Use the Data cleansing tool

Select only 2 columns RegExOut1 and RegExOut2

Make sure the check box Leading and Trailing Whitespace is Ticked.

4. Use the Formula tool to join back

[RegExOut1]+','+[RegExOut2]

This will resolve your issue.

kushalb

I got the solution Shankar when i use Data cleansing and in there remove all whitespaces

it worked.

ShankerV

Welcome @kushalb

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703