Split Data in one column into 2 column in Excel using DOS or VBScript

ETC Microsoft Windows
1

Hi

I have some data in my Excel File.However all the data is in one single column.I want to split it into two columns.

Current Data:

1,Hi Everyone,I am
7,New To Dos,And
17,VB Script,i could
110,have tried this thing
1800,in UNIX

Desired Output
CELL1|CELL 2
1 |Hi Everyone,I am
7 |New To Dos,And
17 |VB Script,i could
110 |have tried this thing
1800 |in UNIX

Basically I wanted to search for first comma and then put content after that to second column.

Share some suggestions pls.
Any DOS command or VB Script will be fine.

2

The scriptname should have .vbs as extension, the output goes to the file Outfile:

Dim filesys, Infile, Outfile, pos

Const ForReading = 1, ForWriting = 2 

file = InputBox("Filename : ")

if file = "" then
  WScript.Quit
end if

Outfile = "Outfile"

Set filesys = CreateObject("Scripting.FileSystemObject")

If NOT filesys.FileExists(file) Then
  msgbox("File " & file & " not found!")
  WScript.Quit
End If 

Set Infile = filesys.OpenTextFile(file, ForReading, True)
Set Outfile = filesys.OpenTextFile(Outfile, ForWriting, True)

do while Infile.AtEndOfStream <> true
  Line = Infile.readline
  pos = instr(Line,",")
  Line = Left(Line, pos - 1) & "|" & Mid(Line, pos + 1)
  Outfile.WriteLine(Line)
loop

Infile.Close
Outfile.Close

Set Infile = Nothing
Set Outfile = Nothing
Set filesys = Nothing

MsgBox("Ready")

Regards

3

Hi Franklin52

Thanks for the reply !
However '|' here does not mean PIPE delimited it means new column in Excel Sheet.
Any inputs ?

4

Replace the pipe symbol with your delimiter on this line:

Line = Left(Line, pos - 1) & "|" & Mid(Line, pos + 1)

For instance, if you want a tab you can use the constant vbTab:

Line = Left(Line, pos - 1) & vbTab & Mid(Line, pos + 1)