How to convert a text with data in series having field name on left to a table in Excel?

Question

I need to convert a dataset in series to a table in Excel. The dataset has field names on the left side in order and each dataset is one after another vertically. I want to have a common primary row with field names on top and the fields data below it one after another. Please tell me how can I do this?

Example:

Dataset:

Name: Max
Age:23
Grade: A
Name: Paul
Age: 24
Grade: B
Name: John
Age:23
Grade:D

To table:

Name	Age	Grade
Max	23	A
Paul	24	B
John	23	D

I tried copying each field to the top row in workbook and copying each data one after another but it is taking long time and I want a quicker way to do this.

Answer 1

Try this formula: in cell E1

=VSTACK(TRANSPOSE(A1:A3),WRAPROWS(B1:B9,3))

Answer 2

You can use this formula:

=LET(data,A1:A9,
cleanData,SUBSTITUTE(data,": ",":"),
tabData,WRAPROWS(TEXTAFTER(cleanData,":"),3),
header,TOROW(TEXTBEFORE(TAKE(cleanData,3),":")),
VSTACK(header,tabData))

It first removes obsolete blanks after the colon.

Then takes the part after the colon - and applies WRAPROWS after each 3rd row.