How to convert lines of words into excel table?

[shl2003]

Hi all,

I am trying to find way to convert words in notepad into excel table. like this:

Notepad:

a1

a2

a3

a4

b1

b2

b3

b4

c1

c2

c3

c4

Excel Table:

a1 a2 a3 a4

b1 b2 b3 b4

c1 c2 c3 c4

Is anybody out there how can give ideas for a solution?

Thx.

[omikron48]

You can try formatting your text into a CSV file and open it up in excel.

[shl2003]

You can try formatting your text into a CSV file and open it up in excel.

Thanks a lot! I have tried your suggestion. But there's only one column in csv, how to convert into multi column ?

I know some excel udf, but unfamiliar with copy and paste in notepad. Do you know some function that can perform this copying and writing to excel job?

Thx.

[omikron48]

CSV (Comma-Separated Values)

Every cell in the table is delimited by a comma. The text file is saved with file extension ".csv".

Sample Text:

Header1,Header2,Header2
Value1,Value2,Value3
ValueA,,ValueB

You can use FileOpen and FileClose to open and save files. You can use FileWrite or FileWriteLine to write to a file.

Edited August 5, 2010 by omikron48

[Sheik]

or you can use

#include "array.au3"
#include "excel.au3"
$var = ControlGetText("[CLASS:Notepad]", "", "Edit1") ;has to exist with >=16 Lines text
$sLines=StringSplit($var,@CRLF,1)
_ArrayDisplay($sLines)
$oExcel = ObjGet("","Excel.Application")    
$oExcel.Workbooks("test.xls").Activate  ;has to be open
_ExcelSheetActivate($oExcel, "Sheet1") ;has to exist

for $r =0 to 3
    for $c=0 to 3
        $x=_ExcelWriteCell($oExcel, $sLines[$r*4+$c+1],$r+1 ,$c+1)
        ;msgbox(0,"Text",$sLines[$r*4+$c+1])
    Next
Next