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
Solved! Go to Solution.
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
Seeking more clarity which you consider as unwanted space.
Space between , British
or space between British Columbia
or space between British Columbia ,
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.
I got the solution Shankar when i use Data cleansing and in there remove all whitespaces
it worked.
Welcome @kushalb