Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.
SOLVED

Remove empty/blank spaces in a cell

Highlighted
7 - Meteor

Hi,

 

Please guide us to remove the empty/blank spaces in a cell. We are attaching the sample for your reference. 

 

Please note we need the same in number format and to be 15 digitis only. e.g. "157872567532303". However, the actual data that we receive contains empty spaces which cannot be even replaced in excel with the help of the replace function. e.g. "    100270021712303".

 

Thank you in advance.

 

Regards,

Neha

Highlighted
15 - Aurora

The DataCleanse tool can help you here.

Highlighted
7 - Meteor

Hi @DavidP 

 

Thank you for the prompt reply. I have tried that but it is not helping me out. Is there any other way out to resolve it?

 

Thanks!

 

Regards,

Neha 

Highlighted
15 - Aurora

Can you explain the exact problem in more detail. If I load your Excel file and attach the Data Cleanse tool as in the example I previously attached, I can see that the leading spaces are removed.

 

The Data Cleanse tool has powerful methods to remove unwanted characters - I just used the standard "Remove leading spaces", but you can also pick the option to remove any character that is not numeric, for instance.

 

What do you need to happen after this?

 

Should the datatype be changed to a numeric type and the result written back to the Excel file, perhaps?

 

If you can provide more details, I'd be able to help better, hopefully.

Highlighted
15 - Aurora

Here's an example that writes the data back to an Excel file in numeric format

Highlighted
7 - Meteor

Hi @DavidP ,

 

Thank you again! Can you please share the screenshot of the cleansing tool what all you selected and what all you deselected from the same?

 

Yes, the data type should be numeric. Can we highlight the cells which are not numeric?

 

I will get back to you on the same soon as I try your solution. As of now I am not able to use Alteryx Tool.

 

Regards,

Neha

Highlighted
15 - Aurora

Below is the option selected in the Data Cleanse tool. Alteryx will evaluate the contents of the whole column of data when initially deciding on the data type. If even 1 cell has characters that are non numeric, it will set the whole column as Text, therefore, because of the leading spaces, the data type for the entire column is set to a String data type.

 

Once the leading spaces are removed, you can then change the data type to a numeric type such as int16, but this has to apply to the entire column, since a column can only have 1 data type. In this case, if there were cells that were not numeric. they will be turned into nulls.

 

DavidP_0-1593180101472.png    DavidP_1-1593180167065.png

 

Highlighted
7 - Meteor

Hi,

 

Thanks @DavidP . I will surely try your solution and get back to you in case of any query.

 

Regards,

Neha

Labels