This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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".
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.
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.