Convert To text format in a column having texts and numbers in multiple records

[db89]

Hi All,
I am trying to find a solution to convert all records in a column, which has mix of texts and numbers, entirely into set of text. 
For Ex. Column A has records As IDs like ABC123, 1234.801,......so on. 
But when I am feeding this to Alteryx, the one with the ID which is 1234.800, is getting converted to 1234.8009999998, which I do not want. 
Is there a solution we can apply as there are multiple entries like this? Any help which would be much appreciated. 

[ShankerV]

Hi @db89 

I got your question, but its too generic.

If you are able to share the sample input and expected output, will be more easy to work on the expected solution which can be inherited in your workflow.

Many thanks

Shanker V

[db89]

@ShankerV thanks for your response. I have attached an input. If you feed that in Alteryx the ID which is 1234.800, is getting converted to 1234.8009999998, which I do not want. So if this can be directly converted into text format, so that the ID does not shows output as "1234.8009999998." Please let me know if that helps. 

[PanPP]

Hi @db89 

Can you share a sample WF with text input and desired output?

Are you trying to remove text from numeric values or?

Have you tried using the Auto Field tool? https://help.alteryx.com/current/AutoField.htm

This tool will convert the columns on input to the proper Alteryx data type.

Hope this helps. 

[db89]

@PanPP thanks for your response. I have attached an input. If you feed that in Alteryx the ID which is 1234.800, is getting converted to 1234.8009999998, which I do not want. So if this can be directly converted into text format, so that the ID does not shows output as "1234.8009999998." Please let me know if that helps. 

[Kakuffo · Accepted solution]

 @db89 I have used a regex approach to first flag any figures without alphabet letters so that you can treat those seperately to the String entries. Then i have used a select tool to apply the fixed decimal data type as this allows you to edit the number of decimal places shown. However if this is not the correct approach you could prepare the numerical entries with a round function or whatever gets you to your standardised result you are looking for. You can union it all back together for the next stage of analysis or reporting. 

solution attached

if this solves your issue please mark this as a solution so others can find the answer quicker

[JamesCharnley]

Hi @db89 

It's being read in as a string since there are non-numerical values within the field, which I'm assuming means that some kind of rounding is happening within the excel, but the full value is being read into Alteryx. 

Since they're now strings, you could use string functions to dynamically take everything up to the '.' and three digits after it, something like this:

[db89]

Hi @JamesCharnley , I have tried this but the only drawback of this if you observe is, "1234.8009999998" should give an out put as "1234.801" and not "1234.800."
I am looking for an alternative to that or if there is something can be done in the formula itself? Thanks you for your response! :)

[JamesCharnley · Accepted solution]

Ah OK @db89! In that case try something like the second formula instead, which can just round the number if it contains a '.':

[db89]

Hi @JamesCharnley,
This worked superbly! Thanks for the guidance and making it so simple! :D