Remove empty/blank spaces in a cell

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

Invalid TRN.xlsx

Topic of Interest

Accepted answers

NehaNevrekar

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

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.

All comments

DavidP

The DataCleanse tool can help you here.

remove leading spaces with data cleanse.yxmd

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

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335