We're excited to announce that we'll be partnering with Credly starting October 19th - see what this means and read the announcement blog here!

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

Tool Mastery | Text To Columns

MattD
Alteryx Community Team
Alteryx Community Team
Created

Text To Columns.pngThis article is part of the Tool Mastery Series, a compilation of Knowledge Base contributions to introduce diverse working examples for Designer Tools. Here we’ll delve into uses of the Text To Columns Tool on our way to mastering the Alteryx Designer:

 

Easily the most used tool in the parsing category, the Text To Columns Tool makes for an extremely quick dicing of delimited fields. To use it you only need to specify a delimited field, delimiter(s), whether you’re parsing to rows or columns (you’ll need to specify a number of columns to parse into with this selection) and you’re off. Any way you slice it, this tool has you covered:

 

Note: The Text To Columns Tool will also accept multi-character delimiters (example attached in MultiDelimiter.yxmd):

 

2019-03-01_9-16-52.png

 

 

When using selection-based tools, the output will often be a concatenated string of all your selections:

 

Pre.png

 

To use the List Box Tool output from the above in your analytic apps (master them here), simply update a placeholding string in a Text Input Tool with an Action Tool (master it here). Then apply some Text To Columns magic to parse the selections into rows of keys that you can easily join(master the art of joining here) back to your input data set for the record filtering.

 

The parsed Interface Tool output will look like the below:

 

post.png

 

The approach in the app should resemble:

2019-03-01_9-37-52.png

 

 

  • Searching for keywords: The Text to Columns Tool is especially useful when building keyword analyses because you can easily split larger strings into rows of single word substrings to join or fuzzy match to for keyword matching.

 

Don’t know how many columns your field will be parsed into? There’s a useful trick to avoid specifying – if you assign a Record ID(master the tool here) and use the Tile Tool (master it here) to count each occurrence of your records (Tile_SequenceNum) you can then Cross Tab (master it here) them back into the table shape you would have had anyway (example attached in ParseIntoRows.yxmd):

2019-03-01_9-28-07.png

 

 

By now, you should have expert-level proficiency with the Text To Columns Tool! If you can think of a use case we left out, feel free to use the comments section below! Consider yourself a Tool Master already? Let us know at community@alteryx.com if you’d like your creative tool uses to be featured in the Tool Mastery Series.

 

Stay tuned with our latest posts every Tool Tuesday by following Alteryx on Twitter! If you want to master all the Designer tools, consider subscribing for email notifications.

Additional Information
Click on the corresponding language link below to access this article in another language -
Portuguese
Spanish
French
German
Japanese

Attachments
No ratings
Comments
gururajb
6 - Meteoroid

How do I include multiple delimiters in the text to column?

here in my case, I want to include numbers from 0 to 9 as delimiters.

Please help.

kmfredrick
5 - Atom

I enjoy the Text To Columns tool! It is one I always find useful in diverse situations. 

TuV
5 - Atom

This tool is such a great help in parsing big data set in Alteryx. There is no need to parse the data in excel before loading it to Alteryx. 

Soorajsu
6 - Meteoroid

This is a great tool for working with CSV files. Very useful

kevin025
8 - Asteroid

A much more advanced version of a similar Excel tool.

abaltazarc
6 - Meteoroid

Excel makes it really dificult when it can be super easy!

jem242
5 - Atom

I have a column that contains the month and year with a field type of String:

 

Period

012012

 

Note there are no delimiters. How can separate the month and year into 2 separate columns?

 

Month     Year

01           2012

 

The original data will always be structured as mmYYYY. This data will later be joined to data that is V_WString. Prior to joining, should I change the field type from String to V_WString to match the data from other sources? It will not be used later for any reason. I am simply trying to have my columns align for the join.

kamanivk
8 - Asteroid

@jem242

 

Instead of Text to Columns, did you explore using substring function?

 

Substring(x, start, length): Returns the substring of (x) starting at (start), stopping after(length), if provided

jem242
5 - Atom

@VamsiK

I decided to combine the others instead. It was the path of least resistance. But I will definitely keep that in mind.

Felipe_Ribeir0
5 - Atom

How can i parse from right to left? C:\Directory1\Directory2\, i need to catch just the last one ("Directory2" in this example) from each row. thanks.

FláviaB
Community Manager
Community Manager

Oi @Felipe_Ribeir0, tudo bem? 

For more visibility, you can always start a new topic in our discussions board to get assistance with your questions. Also, if you haven't checked yet, we have a Community dedicated to our Portuguese speakers. In fact, we do have this Knowledge Base article in there too. 

Happy to see you around!


mjsnyder
7 - Meteor

The text to column tool is a great function, makes the data presentable.

hhusain
6 - Meteoroid

How does the text to column tool handle null values- does it just leave it there?