How can I change raw data, from a List type format to the Table?

Question

I need to process many rows of grouped data which in the list form like this:

Title: Mr.
Name: David
Company: Microsoft

Title: Dr.
Name: John
Company: Facebook

Title: Ms.
Name: Olivia
Company: Google

I want to convert this type of data into a simple Table form like this:

Title     Name      Company
Mr.       David     Microsoft
Dr.       John      Facebook
Ms.       Olivia    Google

I have many tryied with Excel formula and played with these type data to reformat them, but I could not success.

any idiea in Batch, Powershell, Excel method expected.

Thanks

Answer 1

For long dataset it would be best to use Power Query. Alternatively can try below formulas.

=VSTACK({"Title","Name","Company"},TEXTSPLIT(TEXTJOIN("|",1,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1:A3,"Title: ",""),"Name: ",""),"Company: ","")),CHAR(10),"|"))

Note: TEXTJOIN() has character limit 32,767 which is cell character limit of Excel.