Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have approximately 9,000 columns (goes to column MGN) which have 14 rows of data points. I need to get the data in a single column with the format Column B below A, C below B, etc.

Is there any macro or task I can execute so it can be done in one go? Any help would be appreciated.

share|improve this question
up vote 1 down vote accepted

If I understand correctly, then you get a result of 126 000 lines, or slightly more than 2,500 pages of A4 ... If you need to analyze it in person, the idea of ​​a single column is not very good.


xls2col2.vbs Book 1.xlsx

Or drag&drop excel file to vbscript:

drag&drop excel file to vbscript

use Book 1.txt


If WScript.Arguments.Count <1 then 
   Wscript.Echo "Ex.1: cscript xls2col.vbs filename.xls"
   Wscript.Echo "Ex.2: cscript xls2col.vbs filename.xlsx"
End If

Const OpenAsDefault = -2
Const FailIfNotExist = 0
Const ForReading = 1
Const ForWriting = 2

Const xlTextWindows=20
Const xlUnicodeText=42   
Const ExtName = ".txt"

Dim ConvertExt:ConvertExt = xlTextWindows

Dim strFilePath:strFilePath = WScript.Arguments.Item(0) 

Dim FSO:Set FSO = CreateObject("Scripting.FileSystemObject")
    If FSO.FileExists(strFilePath) Then

Dim FileProperties:Set FileProperties=FSO.GetFile(strFilePath)
    Dim strOpenFileName : strOpenFileName = FileProperties.Path
    Dim FileConvertName : FileConvertName = FSO.BuildPath(FileProperties.ParentFolder, FSO.GetBaseName(FileProperties) & ExtName)

Dim ExcelApplication:Set ExcelApplication = CreateObject("Excel.Application")

ExcelApplication.DisplayAlerts = FALSE
ExcelApplication.Visible = FALSE

ExcelApplication.Workbooks(1).SaveAs FileConvertName, ConvertExt

WScript.Sleep 5000

Set FSO = CreateObject("Scripting.FileSystemObject")
Dim file: Set file = FSO.OpenTextFile(FileConvertName, ForReading, FailIfNotExist, OpenAsDefault)

Dim FileReadAll: FileReadAll = file.ReadAll
FileReadAll = Replace(FileReadAll, Chr(9), Chr(13)&Chr(10))

Set file = FSO.OpenTextFile(FileConvertName, ForWriting, True)

      WScript.Echo "File Open Error: file not exist!"
End If

This script saves your file first in the text, and then it converts the text in it in one column. If it is needed it is in excel format, open the text file and resave, but I do not see the need.

EViews 7 txt file:

EViews 7 txt file

share|improve this answer
That would be correct. I would have close to 126,000 rows solely in column A. The data is being imported to EViews 7 so I am formatting it for panel data. – nvdkgm Mar 16 '13 at 16:51
@nvdkgm ok, see and test script – STTR Mar 16 '13 at 20:01
Appreciate the help and the screenshots. – nvdkgm Mar 17 '13 at 6:31

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.