Link to home
Start Free TrialLog in
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.
Avatar of [ fanpages ]
[ 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.
Avatar of Charles Meyer

ASKER

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
Avatar of [ fanpages ]
[ fanpages ]

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Perfect!

Thank you so much for your time and effort. Saved us so much time!
You're very welcome.

Good luck with the rest of your project.