Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Tool Mastery

Explore a diverse compilation of articles that take an in-depth look at Designer tools.
Become a Tool Master

Learn how you can share your expertise with the Community

LEARN MORE

Tool Mastery | Text To Columns

MattD
Alteryx Alumni (Retired)
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
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
16 - Nebula

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
Alteryx Community Team
Alteryx Community Team

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.

Inactive User
Not applicable

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

Tyrane782001
7 - Meteor

I agree, the text to column is very helpful, even split the rows

mvtejano
7 - Meteor

This is interesting and very helpful. Thank you.

srilakshmi123
7 - Meteor

hhtps://community.altery.com/t5/alteryx-community-english/ct-p/externa

Anitha1289
7 - Meteor

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. 

 
 
 
 
GuthurthiRaju
6 - Meteoroid

cool

KARNAILSINGH
6 - Meteoroid

I also enjoying the text to column tools to separate the one column into multiple

nadiralam
7 - Meteor

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. 

SeanBain1965
5 - Atom

Why create 5 columns in the first example when it only required 4?

NeilR
Alteryx Alumni (Retired)

@SeanBain1965 Because there were two colons in the original field, which resulted in an extra empty column (which is later cleaned up with the Dynamic Select tool).

muradmusleh
6 - Meteoroid

I found it helpful.

I think there is a missing step before dynamic rename. The input should be sorted by column 1 descendingly then use dynamic rename. After that we may do sort the ID column ascendingly. 

Mgaurav2grt
7 - Meteor

very informative