Forum Discussion

mattmorgan1983's avatar
mattmorgan1983
Copper Contributor
Dec 27, 2022

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...
Excel
mattmorgan1983's avatar
mattmorgan1983
Copper Contributor
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!
Lorenzo's avatar
Lorenzo
Silver Contributor
Dec 28, 2022

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

Resources