community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Text to columns problem

Meteor

I am a new user and am having problems with the "Text to Columns" function. I am trying to separate a date into 3 columns so I can isolate the year. In Excel the dates look like this: 1/31/2018. However, when I view my work in the Browse function, the dates look like this: 2018-01-31. I have tried using the / delimiter and the - delimiter, but neither works. Each time I get 4 new columns: the first is a replica of my original "Date Placed in Service" column followed by 3 completely empty columns. I have placed the Text to columns in several locations of my workflow including immediately following the Input Data. The results are always the same. What am I doing wrong. 

 

 

Alteryx Certified Partner
Alteryx Certified Partner

Hi @LStevenson 

 

Why don't you use DateTimeYear function instead? Just add a simple formula Tool and this function will do the job.

 

Cheers,

Alteryx Certified Partner

From your description of "1/31/2018 in Excel is 2018-01-31 in Browse" it sounds like it's being read as a Date field and Alteryx is displaying that date in it's native format (ISO 8601). if this is the case and the field is already a "Date"/"DateTime" field type, you can use the DateTimeFormat( function in a formula tool to return just the part of the date you're interest in. 

 

To return a string value of "2018":

DateTimeFormat([Date],"%Y")

 

To return a numeric value of 2018:

tonumber(DateTimeFormat([Date],"%Y"))

 

This documentation page contains info on how to parse date time fields in Alteryx:

https://help.alteryx.com/10.1/Reference/DateTimeFunctions.htm

Highlighted
Alteryx
Alteryx

Hi @LStevenson - looks like Alteryx automatically converted the excel date format to its own which is yyyy-mm-dd. So you can use the select tool and convert the date into a string type and your text to columns tool will work. Alternatively, you can use the Formula tool and the DateTimeYear expression to isolate the year. Hope that helps!

Labels