text matrix translation

nir_k

New Member
Joined
Oct 2, 2005
Messages
16
Hi experts,

i have a challenge.

i have a text file with the following data


12345
-----
A |xxxxx
A1 |.x...
A2 |..x.x

I need to get an output better on text (excell is limited with rows and the matrix can have up to 5000 rows and lines)
i tried to open the file with excel but every line is a string and i can't separet it

the output will be

A 1,2,3,4,5
A1 ,2,
A2 ,3, ,5


it will save me a lot of time

tnx

Nir
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
try
Code:
Sub test()
Dim fn As String, delim As String, ff As Integer
Dim a(), i As Long, n As Long, txt As String
delim = ","  '<- change here for delimiter of .txt file
fn = "c:\test\test.txt"  '<- change here
ff = FreeFile
Open fn For Input As #ff
     Do While EOF(ff)
          Line Input #ff, txt
          n = n + 1
          ReDim a(1 To n)
          a(n) = Split(txt, delim)
     Loop
Close #ff
With ThisWorkbook.Sheets(1).Range("a1")
     For i = 1 To n
          .Offset(i-1).Resize(,UBound(a(i))+1).Value = a(i)
     Next
End With
End Sub
 
Upvote 0
tnx for the repalay,

i tried to run it on

123456789
----------
A |xxxxxxxxxx
A1 |.x........
A2 |..x.....x.
A3 |..xx......
A4 |...xx.....
A5 |x...x.....
A6 |.....x....
A7 |......x...
A8 |...x...x..
A9 |.......x..
A10|........x.


but it didnt work
is it suppose to open a new text file?

Nir
 
Upvote 0
Jindon,

Sorry i dont understand what you mean
my text file looks like this (just copy it to *.text file)
123456789
----------
A |xxxxxxxxxx
A1 |.x........
A2 |..x.....x.
A3 |..xx......
A4 |...xx.....
A5 |x...x.....
A6 |.....x....
A7 |......x...
A8 |...x...x..
A9 |.......x..
A10|........x.

i didnt open the text file with excel becouse every line in the text file goes into one cell and ican't separet it

so i dont andeerstand what you ment by
delim = "," '<- change here for delimiter of .txt file

what should it be now that you know how my text file looks

tnx

Nir
 
Upvote 0
Try Data--> Import Extarnal Data --> Import Data, select your file, and make sure the file type is delimited, choose the row number to start the import from, I assume from your data that it will be row 3.

Click next, and in delimiters untick the comma box, and under Other : type in the pipe symbol (in other words the | symbol, copy and paste the one here if you are unsure).

Click finish and select where you want to copy it to.

Does this solve it for you ?
 
Upvote 0
If it is exactly so.
Code:
Sub test()
Dim fn As String, delim As String, ff As Integer
Dim a(), i As Long, n As Long, txt As String, x, y, z()
delim = "."  '<- change here for delimiter of .txt file
fn = "c:\test\test.txt"  '<- change here
ff = FreeFile
Open fn For Input As #ff
     Do While EOF(ff)
          Line Input #ff, txt
          x = Split(txt,"|")
          y = Split(x(1),delim)
          ReDim z(UBound(y) + 1)
          z(0) = x(0)
          For i = 0 To UBound(y)
               z(i+1) = y(i)
          Next
          n = n + 1
          ReDim a(1 To n)
          a(n) = z
     Loop
Close #ff
With ThisWorkbook.Sheets(1).Range("a1")
     For i = 1 To n
          .Offset(i-1).Resize(,UBound(a(i))+1).Value = a(i)
     Next
End With
End Sub
 
Upvote 0
Jindon,

Please discribe me the process what i need to do

i copy the code into the excel in a new modul

i have a text file name test.txt with the matrix in c:\temp

and...

i believe uts somthing i do wrong

when i follow the code it's entering the "Do While EOF(ff)" loop
but goes to the end of the code

tnx

nir
 
Upvote 0
Sorry my mistake
Few changes made
Code:
Sub test()
Dim fn As String, delim As String, ff As Integer
Dim a(), i As Long, n As Long, txt As String
delim = "."  '<- change here for delimiter of .txt file
fn = "c:\temp\test.txt"
ff = FreeFile
Open fn For Input As #ff
     Do While Not EOF(ff) '<- this line
          Line Input #ff, txt
          txt = Replace(txt,"|",delim)
          n = n + 1
          ReDim a(1 To n)
          a(n) = Split(txt,delim)
     Loop
Close #ff
With ThisWorkbook.Sheets(1).Range("a1")
     For i = 1 To n
          .Offset(i-1).Resize(,UBound(a(i))+1).Value = a(i)
     Next
End With
End Sub
 
Upvote 0
tnx for the time

istill have a debag massege

in this line

.Offset(i - 1).Resize(, UBound(a(i)) + 1).Value = a(i)


nir
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top