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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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
 

nir_k

New Member
Joined
Oct 2, 2005
Messages
16
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
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
How is your txt file delimited?
Post your code with the change
 

nir_k

New Member
Joined
Oct 2, 2005
Messages
16
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
 

Thorin

Board Regular
Joined
Feb 8, 2007
Messages
243
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 ?
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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
 

nir_k

New Member
Joined
Oct 2, 2005
Messages
16
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
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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
 

nir_k

New Member
Joined
Oct 2, 2005
Messages
16
tnx for the time

istill have a debag massege

in this line

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


nir
 

Forum statistics

Threads
1,181,421
Messages
5,929,805
Members
436,696
Latest member
Mr Rice

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
Top