How to import raw .txt data into Excel

New Contributor

I have files of raw testing data in .txt form that I need to import into Excel spreadsheets. 


The .txt files are formatted as follows: 




(The letter plus number on the left indicates student code, and the subsequent letter (ABC or D) indicates option selected on a test). 


Is there a way to convert this to Excel so that each answer (ABC or D) gets its own column? I'm struggling to find a solution. Thanks! 

7 Replies
could you please share txt file? we ll get informant




You can try this formula if you work with Office365.

split to columns.JPG


If you run Excel >/= 2016 on Windows that sounds doable with Get & Tranform aka Power Query:


Thanks everyone for your help! I was able to get this sorted using the data tab, then insert data from CSV/TXT. I then did the split column feature to separate out user ID and individual responses. But next time I’ll try the methods you described, as I’m sure there’s a number of ways to do this.

Thanks again!


Assuming that all the numbers will always be in a sequence to the left and there are no stray numbers within the multiple choice area -

I am also assuming that your .txt converts each student to a single cell of data (e.g. A1), then this should do the trick (though Quadruple_Pawn's response is much better if you have Lambda):


Great Lambda function! I'd suggest replacing the '12' in the drop function with a dynamic solution IF the student's ID is not 12 letters/digits long.


Forgot to attach the Power Query option yesterday (apologies) - post edited


Another 365 option:


in C1:



  SplitStudAnsw, LAMBDA(string,array,
      x,       MIN(SEARCH(array,string,2))-1,
      answers, MID(string,x+1,LEN(string)-x),
      HSTACK(LEFT(string,x), MID(answers,SEQUENCE(,LEN(answers)),1))
  Answers, CHAR(SEQUENCE(4,,97)),
  SplitEach, REDUCE("",Strings,
    LAMBDA(seed,str, VSTACK(seed,SplitStudAnsw(str,Answers)))
    HSTACK("Student", "A" & SEQUENCE(,COLUMNS(SplitEach)-1)),