Forum Discussion
How to import raw .txt data into Excel
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: X47312804237ACDBBADCADBCDBDCCBDAACCDACBA (The lette...
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):
=LET(DATA,A1,
SPREAD,MID(DATA,SEQUENCE(LEN(DATA)),1),
NTHPOS,IFERROR(MATCH(1E+100,INDEX(--SPREAD,)),0),
TRANSPOSE(INDEX(SPREAD,SEQUENCE(LEN(DATA)-NTHPOS,1,NTHPOS+1,1))))