Text to column for multiple fields at once

[Noorakroush]

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

[LordNeilLord · Accepted solution]

Hey @Noorakroush 

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

[MichaelLaRose]

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