Excel - COLUMN Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

How do I combine multiple text files into a single worksheet but with different columns for each files? I need 2 columns for each file and all the files need to be aligned side by side. For example, I need data in txt1 to be loaded into column A and B, txt2 in column C and D, and txt3 in column E and F, and so on. I have total 60 txt files to be combined into a single spreadsheet.
Solved by E. C. in 24 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 21/06/2018 - 10:42
User 21/06/2018 - 10:43
Excelchat Expert 21/06/2018 - 10:43
Welcome to Got it Pro
Excelchat Expert 21/06/2018 - 10:44
You will have to copy paste the data from each file and paste it into respective columns
User 21/06/2018 - 10:44
can you understand my question?
Excelchat Expert 21/06/2018 - 10:44
Yes i understand it
User 21/06/2018 - 10:45
Is there any other way? There are 60 files
Excelchat Expert 21/06/2018 - 10:46
I understand..may be we can do it through writing a macro code but unfortunately this platform does not support VBA/macros
User 21/06/2018 - 10:46
and i have to do it many times and each time I need to handle 60 files
User 21/06/2018 - 10:46
I thought the power query may be one solution
Excelchat Expert 21/06/2018 - 10:48
yes power query can also work but you will need to setup it such a way that you get the result that you want
User 21/06/2018 - 10:50
will you be able to help me to set up the power query?
Excelchat Expert 21/06/2018 - 10:51
Excelchat Expert 21/06/2018 - 10:52
This is the basic on power queries
Excelchat Expert 21/06/2018 - 10:53
First of all we will need to install power query
Excelchat Expert 21/06/2018 - 10:53
User 21/06/2018 - 10:53
does it mention anything similar to what I want? like the arrangement of my data?
Excelchat Expert 21/06/2018 - 10:53
This will help with setting up of power query tab in the excel
Excelchat Expert 21/06/2018 - 10:54
not exactly
User 21/06/2018 - 10:54
I already have power query in my excel 2016
Excelchat Expert 21/06/2018 - 10:54
If i had this question i would have either used VBA or done it manually if it was a one time exercise
Excelchat Expert 21/06/2018 - 10:55
There could be one more method to it...
Excelchat Expert 21/06/2018 - 10:55
We can connect all the text files to excel so that their output comes in different columns
Excelchat Expert 21/06/2018 - 10:56
This would be the easiest method
User 21/06/2018 - 10:56
how to do that?
Excelchat Expert 21/06/2018 - 10:57
Select the cell where the first line of text from the file should be. Use the Data/Get External Data/From File dialog to select the text file to import. Format the imported text as required. In the Import Data dialog that opens, click on Properties... Uncheck the Prompt for file name on refresh box. Whenever the external file changes, click the Data/Get External Data/Refresh All button.
Excelchat Expert 21/06/2018 - 10:58
i think this should help you, you can embed the output of text files in excel through this way
User 21/06/2018 - 11:02
Do I need to do it for each text file? Or I can do it one time for all 60 files?
Excelchat Expert 21/06/2018 - 11:03
This we will have to do for each text file..
Excelchat Expert 21/06/2018 - 11:03
We can refresh it so that next data is displayed once the text files are updated
User 21/06/2018 - 11:05
can you show me how to do it? I can upload 2 txt files.
Excelchat Expert 21/06/2018 - 11:05
I have mentioned the steps, we will need to follow them
Excelchat Expert 21/06/2018 - 11:05
I think it cannot be done on google sheets
Excelchat Expert 21/06/2018 - 11:06
Even if i do it on excel at my end it won't help you
Excelchat Expert 21/06/2018 - 11:06
Hope you understand
User 21/06/2018 - 11:06
I see. Thank you for your help.
Excelchat Expert 21/06/2018 - 11:06
Hope this session has helped you
Excelchat Expert 21/06/2018 - 11:06
Please do give good rating and reviews..
Excelchat Expert 21/06/2018 - 11:07
It would really help a lot
Excelchat Expert 21/06/2018 - 11:07
Have a great day
Excelchat Expert 21/06/2018 - 11:07
Take Care
User 21/06/2018 - 11:07
thank and you too. bye now.

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc