Import text into multiple columns Excel

**sacblanc** · 02-07-2020, 01:55 PM

Hi there,

I am trying to figure out if there is a way to do the following.

I have a text document containing various data sorted in lines, which looks something like this :

Data 1.A
Data 1.B
Data 1.C
Data 1.D
Data 2.A
Data 2.B
Data 2.C
Data 2.D
Data 3.A
Data 3.B
Data 3.C
Data 3.D

And so on.

I would like to import this text document into Excel and have every ".A"-ending lines sorted into separate rows of column A, every ".B"-ending lines sorted into separate rows of column B, etc.

Is that something that can be done ?

Thanks in advance for the followup and have a great day.

Best regards,
Etienne

**PeteABC123** · 02-07-2020, 04:23 PM

Depending on complexity and time you want to invest:
Assumes your data resides in col A.
Run your formulas, paste all back as values, and delete your column A.
Place this into B1:

Please Login or Register  to view this content.

copy down
Place this into C1:

Please Login or Register  to view this content.

"
Place this into D1:

Please Login or Register  to view this content.

"
Place this into E1:

Please Login or Register  to view this content.

"

Pete

**sacblanc** · 02-09-2020, 01:19 PM

Hi there,

PeteABC123 : thanks for the suggestion. This formula doesn't seem to work in my case, though. I believe this is because the data in my text document doesn't really end by "A", "B", "C" and "D", like the example in my first post suggested. This was only a way for me to express that there were different "categories" of data that repeat themselves on a regular basis in my document (five categories, to be more precise).

I have now imported all the text data in column A of an Excel document. Hence, what I am trying to do now, is to gather the data residing in A1, A6, A11, A16 and so forth into column B, the data in A2, A7, A12, A17, etc. into column C, the data in A3, A8, A13, A18, etc. into column D, the data in A4, A9, A14, A19, etc. into column E and the data in A5, A10, A15, A20, etc. into column F.

Is there a way to do this without having to link every cell individually ?

Thanks in advance for the help.

Best,
Etienne

**PeteABC123** · 02-09-2020, 01:57 PM

Establish what the different criteria is in each cell in column A. Instead of using the RIGHT function, use wildcard characters "*". . .if cell contains etc. Might have your criteria at the top of the column you want the results brought into, and copy down.

HTH.

Pete

**sacblanc** · 02-09-2020, 02:55 PM

Hi there,

Thanks again for the help.

It seems that this will not resolve the problem, as there are no "defining" or "different" characteristics to the cells in column A. They are only made of text, with no redundancy among it.

The easiest solution that I see would be to refer to =A1 in B1, =A6 in B2, =A11 in B3, and then copy down B1, B2 and B3 until I cover all the range of column A, but Excel doesn't seem to "understand" the logic of this manipulation (i.e. refer to cells in column A with 5-rows jump increments) when I do the copy down.

Is there anything else I could try ?

Best,
Etienne

**Gregb11** · 02-09-2020, 11:25 PM

Try putting this formula in B1, and then copy down and across.
=IF(OFFSET($A1,(ROW()-1)*4+COLUMN()-2,0)=0,"",OFFSET($A1,(ROW()-1)*4+COLUMN()-2,0))

Just going off you post #3, it should work for that.
Does this help?

Import text into multiple columns Excel

LinkBack

Thread Tools

Rate This Thread

Display

Import text into multiple columns Excel

Re: Import text into multiple columns Excel

Re: Import text into multiple columns Excel

Re: Import text into multiple columns Excel

Re: Import text into multiple columns Excel

Re: Import text into multiple columns Excel

Thread Information

Users Browsing this Thread

Similar Threads

import text columns into excel

Import from text files and split into columns in Excel

Import text file that contains rows into excel as columns

Text file to excel columns import

Macro to Parse Text - Import text to Excel from Multiple Text Files & Folders

Import Text File Ddata Into Excel Spreadsheet - Multiple Columns & Rows

Import Text file Fields as Columns in Excel

Bookmarks

Bookmarks

Posting Permissions