Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Remove empty/blank spaces in a cell

NehaNevrekar
8 - Asteroid

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

10 REPLIES 10
DavidP
17 - Castor
17 - Castor

The DataCleanse tool can help you here.

NehaNevrekar
8 - Asteroid

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 

DavidP
17 - Castor
17 - Castor

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.

DavidP
17 - Castor
17 - Castor

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

NehaNevrekar
8 - Asteroid

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

DavidP
17 - Castor
17 - Castor

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

 

NehaNevrekar
8 - Asteroid

Hi,

 

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

 

Regards,

Neha

NehaNevrekar
8 - Asteroid

Hi @DavidP ,

 

I tried the solution given by you. However, the result is not what we expected.

 

I am attaching the original data renamed as "Earlier" and the revised data renamed as  "After applying Cleansing data". Further, also the results i.e. the Output of both the data is also attached under "Results" column.

 

I am also attaching the formula for your reference. Kindly guide me asap.

 

Regards,

Neha

NehaNevrekar
8 - Asteroid

Hi @DavidP ,

 

Awaiting your reply.

 

Thanks!

 

Regards,

Neha

Labels