Remove empty/blank spaces in a cell

[NehaNevrekar]

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

[DavidP]

The DataCleanse tool can help you here.

[NehaNevrekar]

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]

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]

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

[NehaNevrekar · Accepted solution]

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

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.

    

[NehaNevrekar]

Hi,

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

Regards,

Neha

[NehaNevrekar]

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]

Hi @DavidP ,

Awaiting your reply.

Thanks!

Regards,

Neha