How can I automatically organize data from a text file in a spreadsheet?

user2738229

I have a text file like this:

raw data

and I would like to organize into Excel like this:

excel data

I could do this by hand manually for two data entries, but I would like to be able to automate this for a larger scale. What I've tried so far is to input the data into Excel using its built-in feature, but that's where I'm stuck. I tried to transpose the columns into rows, but that doesn't neatly organize it like I want it. Do I need to do some coding or is there a built-in function? If I do need coding, could someone point me in the right direction?

Thanks for your time!

Mike Honey

I would try the Power Query Add-In for this. It has powerful commands for manipulating data. There are functions underneath, but you can achieve a lot by just clicking the buttons in the Power Query window. Working visually in Power Query is usually much easier than coding in VBA.

I had a go at this particular challenge and got it working. You can download and use my demo file from my OneDrive:

http://1drv.ms/1AzPAZp

It's the file: Power Query demo - reorganising rows into columns.xlsx

As described on the ReadMe sheet, I only had to write one simple function - the rest was just clicking around the UI. Power Query is great!

To use it against your own text files, just Edit the first Step in the Query to point to your file. BTW you can even get Power Query to loop over the files in a folder if required.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-03-25

Comments

0 comments

How do I automatically take data from a Google form and generate a new spreadsheet from each response then have it automatically format it?

How can I scrape this table properly or order this data properly so that it's not a bunch of mumbo jumbo and organize it in a csv file?

TOP Ranking

Article

How can I automatically organize data from a text file in a spreadsheet?

How can I automatically organize data from a text file in a spreadsheet?

Loopback Error: connect ECONNREFUSED 127.0.0.1:3306 (MAMP)

Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

pump.io port in URL

How to import an asset in swift using Bundle.main.path() in a react-native native module

Failed to listen on localhost:8000 (reason: Cannot assign requested address)

Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

Emulator wrong screen resolution in Android Studio 1.3

3D Touch Peek Swipe Like Mail

Double spacing in rmarkdown pdf

Svchost high CPU from Microsoft.BingWeather app errors

How to how increase/decrease compared to adjacent cell

Using Response.Redirect with Friendly URLS in ASP.NET

java.lang.NullPointerException: Cannot read the array length because "<local3>" is null

BigQuery - concatenate ignoring NULL

How to fix "pickle_module.load(f, **pickle_load_args) _pickle.UnpicklingError: invalid load key, '<'" using YOLOv3?

ngClass error (Can't bind ngClass since it isn't a known property of div) in Angular 11.0.3

Can a 32-bit antivirus program protect you from 64-bit threats

Make a B+ Tree concurrent thread safe

Bootstrap 5 Static Modal Still Closes when I Click Outside

Vector input in shiny R and then use it

Assembly definition can't resolve namespaces from external packages