Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here
SOLVED

Text to column for multiple fields at once

Highlighted
7 - Meteor

Hello experts, 

 

I have data that looks like the following and I would like to split multiple fields to columns at the same time. Bearing in mind that some records would require the split to be into 2 columns, some to 3 and so on . I illustrate below 

 

StudentIDNameTitlepositionDegreeDegreeDateDegree University
1axyBS, MSc, PhD2012,2014,2020AUC, AUC, NYU
2bwuBS, MSc2008,2012UT, UVA
3cmnBS2002PennState

 

I want the output to look like this 

 

StudentIDNameTitlepositionDegree_1Degree_2Degree_3DegreeDateDegreeDateDegreeDateDegree University_1Degree University_2Degree University_3
1axyBSMScPhD201220142020AUCAUCNYU
2bwuBSMSc 20082012 UTUVA 
3cmnBS  2002  PennState  
Highlighted
Alteryx Certified Partner

Hey @Noorakroush 

 

In these circumstances its always easier to deal with the data if you transpose it first:

 

Capture.JPG

Highlighted
10 - Fireball

Hi @Noorakroush 

 

See attached.

 

Essentially what you need to do is create a shared column for each set of values and split the columns to rows.

 

Once you've done that it is just a matter of identifying the number of new column headers and you're all set.

 

Best,

Michael

Labels