Avatar of Charles Meyer
Charles MeyerFlag for New Zealand asked on

Excel - break one column into multiple columns

We have several text documents what look something like this:
4ED
Group 1: ITE-1
Group 2: ITE-2
Group 2: ITE-6
Group 2: DFG-1

7DF
Group 1: RDA-1
Group 2: ITE-2

DEF
Group 1: SIMON
Group 2: LPI
Group 2: K88TS
Group 2: HOUSE1

ZDF
Group 1: LPI

Open in new window


The list can be several thousand lines long and the blocks of data are different sizes as shown.

I have been tasked with entering the data into a spreadsheet with each block of data being a new column.

Does anyone know if it is possible to create a macro (or use an existing Excel feature) that will be able to move each block of data to a new column? Perhaps a rule could be created where the macro sees the blank cell between each block then cuts and pastes the data between each blank cell to a new column.

Sorry for rambling.
Microsoft ExcelSpreadsheets

Avatar of undefined
Last Comment
[ fanpages ]

8/22/2022 - Mon
[ fanpages ]

Hi,

No need to apologise :)

Just for clarity, with the file contents shown above, the expected outcome is as follows:

Column [ A ]
4ED
Group 1: ITE-1
Group 2: ITE-2
Group 2: ITE-6
Group 2: DFG-1
---
Column [ B ]
7DF
Group 1: RDA-1
Group 2: ITE-2
---
Column [ C ]
DEF
Group 1: SIMON
Group 2: LPI
Group 2: K88TS
Group 2: HOUSE1
---
Column [ D ]
ZDF
Group 1: LPI
---

Is that correct?

The list can be several thousand lines long and the blocks of data are different sizes as shown.

Which version of Microsoft Excel are you using?

I am conscious that there may be potential for a text file's contents to not be able to be represented after splitting each block of data if the number of columns exceeds those supported by MS-Excel.

Finally, are you looking to completely automate the retrieval of the contents of every text file to be processed, or to be able to represent the data within a text file being viewed in MS-Excel at any given time?

That is, do you wish to open every text file in a specific folder & produce the transposed output within separate workbooks (or within multiple worksheets within one single workbook), or be able to open a single text file & then request the manipulation of the contents manually?

BFN,

fp.
ASKER
Charles Meyer

Thank you for the quick reply.

Your are correct with the layout of the columns.
The Excel versions range from 2003 to 2010.

I'm looking for a manual process, as you mentioned we will run out of columns, so I will need to run the process until we hit the limit and then create a new sheet to continue with.
ASKER CERTIFIED SOLUTION
[ fanpages ]

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Charles Meyer

Perfect!

Thank you so much for your time and effort. Saved us so much time!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
[ fanpages ]

You're very welcome.

Good luck with the rest of your project.