Data Cleansing Query

[kushalb]

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

[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

Canada,British Columbia

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 · Accepted solution]

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

it worked.

[ShankerV]

Welcome @kushalb