Convert To text format in a column having texts and numbers in multiple records
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Solved! Go to Solution.
- Labels:
- Preparation
- Reporting
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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! :)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Ah OK @db89! In that case try something like the second formula instead, which can just round the number if it contains a '.':
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @JamesCharnley,
This worked superbly! Thanks for the guidance and making it so simple! :D
![](/skins/images/33644471E927638F516C93A75ADDEE18/responsive_peak/images/icon_anonymous_message.png)