SOLVED
Text to column for multiple fields at once
Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
Noorakroush
7 - Meteor
‎07-08-2020
08:30 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
StudentID | Name | Title | position | Degree | DegreeDate | Degree University |
1 | a | x | y | BS, MSc, PhD | 2012,2014,2020 | AUC, AUC, NYU |
2 | b | w | u | BS, MSc | 2008,2012 | UT, UVA |
3 | c | m | n | BS | 2002 | PennState |
I want the output to look like this
StudentID | Name | Title | position | Degree_1 | Degree_2 | Degree_3 | DegreeDate | DegreeDate | DegreeDate | Degree University_1 | Degree University_2 | Degree University_3 |
1 | a | x | y | BS | MSc | PhD | 2012 | 2014 | 2020 | AUC | AUC | NYU |
2 | b | w | u | BS | MSc | 2008 | 2012 | UT | UVA | |||
3 | c | m | n | BS | 2002 | PennState |
Solved! Go to Solution.
Labels:
- Labels:
- Transformation
2 REPLIES 2
LordNeilLord
15 - Aurora
‎07-08-2020
08:41 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @Noorakroush
In these circumstances its always easier to deal with the data if you transpose it first:
MichaelLaRose
10 - Fireball
‎07-08-2020
08:41 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
