 New Contributor

# 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:

(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

# Re: How to import raw .txt data into Excel

could you please share txt file? we ll get informant

# Re: How to import raw .txt data into Excel

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

You can try this formula if you work with Office365. # Re: How to import raw .txt data into Excel

If you run Excel >/= 2016 on Windows that sounds doable with Get & Tranform aka Power Query: # Re: How to import raw .txt data into Excel

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!

# Re: How to import raw .txt data into Excel

@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,

# Re: How to import raw .txt data into Excel

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.

# Re: How to import raw .txt data into Excel

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,