How to import raw .txt data into Excel

[mattmorgan1983]

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 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! 

[#2]

could you please share txt file? we ll get informant

[#3]

@mattmorgan1983 

=DROP(TRANSPOSE(BYROW( SEQUENCE(LEN(A1)), LAMBDA(x, MID(A1, x, 1) ) )),,12)

You can try this formula if you work with Office365.

[#4]

@mattmorgan1983 

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

[#5]

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!

[#6]

@mattmorgan1983

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))))
 

[#7]

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.

[#8]

@mattmorgan1983 

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

Another 365 option:

in C1:

=LET(
  SplitStudAnsw, LAMBDA(string,array,
    LET(
      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)))
  ),
  VSTACK(
    HSTACK("Student", "A" & SEQUENCE(,COLUMNS(SplitEach)-1)),
    IFNA(DROP(SplitEach,1),"")
  )
)