_x000D_ in the input file

[ivoiculescu2020]

Hello team,

I noticed this _x000D_ for some lines in the Excel file I use for one of my workflows. I looked at the Excel file itself but this does not appear. I also checked if there are any blank spaces in these lines or any hidden characters that might explain this odd "addition" to the fields but there is nothing.

I tried the data cleansing tool first, then regex_replace, then remove the formula to remove the extra characters. Nothing works. 

Can you please advise why this _x000D_ shows up? It's always at the end of the field. 

Example: this _x000D_ shows in the Lot # column on the input file at the end of the field. 

Lot # 12-3456_x000D_

I didn't see it in the beginning or middle of the field.

Any suggestions on how I can resolve it?

Thank you!

Regards,

Ioana

[BRRLL99]

The x000D you're seeing in your Alteryx workflow is a special character that represents a carriage return (CR) or line break in certain contexts. In this case, it seems like there might be an issue with the line endings in your input file. The x000D is a representation of the carriage return character that is commonly used in Windows-based systems.

To resolve this issue, you can try the following steps:

1. Text to Columns: Use the Text to Columns tool in Alteryx to split the Lot # column into multiple columns based on a delimiter. Choose the delimiter as "x000D" (without quotes) to separate the field and remove the x000D portion.

2. Formula Tool: After splitting the column, you can use the Formula tool to concatenate the split columns back into a single column. Use the formula Trim([Column1]) + Trim([Column2]) to remove any leading or trailing spaces and combine the columns.

3. Data Cleansing: Apply the Data Cleansing tool after concatenating the columns to remove any leading or trailing spaces that might be present.

4. Trim: In some cases, the x000D might be surrounded by spaces, causing it to be hidden. To remove any hidden spaces, you can use the Trim function in the Formula tool. Apply the Trim function to the Lot # column to remove any leading or trailing spaces.

[binuacs]

@ivoiculescu2020 what was the regex function you used to remove the character _x000D_? would you be able to provide a sample file with this character?

[ivoiculescu2020]

@BRRLL99

Thank you so much for your prompt reply.

I did as you advised but I noticed another issue when I try to trim it. 

For example: lot #23-BU-00750 ends with a "0" as you can see. If I trim it, it cuts the 0

This is the formula that I use:

TrimRight([LOTID],"x000D_")

and the result is: 23-BU-0075

Any lot that ends with a "0" will be in the same situation.

Any ideas?

Thanks,

Ioana

[binuacs · Accepted solution]

@ivoiculescu2020 try the formula 

Replace([LOTID],"x000D_","")

[ivoiculescu2020]

@binuacs and @BRRLL99 thank you so much for your prompt assistance! your solutions worked!!!