TextToColumn or something else will solve this?

I have some text files with this type of strings

abc,cde:def;ghi
jkl.mno>pqr^stu
vwx,yza:bcd;efg
hij.klm>nop^qrs


and so on and so on

Every two lines has the same pattern and there are at least 100 lines per txt file. Strings has no spaces, no numeric in between only the above six delimeters.
What I want is to bring every two lines from txt files one by one to the one row of worksheet like show in the snap

Note: All txt files are named as
Machine 1
Machine 2
Machine 3 and so on

Maybe :

Please Login or Register  to view this content.

Hi karedog

Thanks for the effort but I need to wait for Monday as I don't have access to the text files mentioned in the thread. I will let you know about result of your code on Monday.

Ok, so until Monday then ....

All right. I overlooked your folder with sample material and solution applied.
Much appreciated

It is exactly what I wanted.

Many thanks indeed

Another question just for knowledge

Please Login or Register  to view this content.

We were taught that only a collection can be looped through like the above. What v is actually (in this example it variant) but is it an object. I mean for each object in Array?

Or

Is an array also a collection?

You are welcome, thanks for marking the thread as solved and rep.points.


As for :

Originally Posted by ImranBhatti

Another question just for knowledge

We were taught that only a collection can be looped through like the above. What v is actually (in this example it variant) but is it an object. I mean for each object in Array?

Or

Is an array also a collection?

No, who told you that information ?

Please open the VBA window, highlight the Each word and then press F1 to bring out the help window.

Microsoft will provide this information on the help :

Repeats a group of statements for each element in an array or collection.


So, yes indeed the For Each is also applied for array too.

Thanks learnt something new. I used to loop through array with For....Next.

You are welcome.

Just remember, looping using ForEach for array of simple datatypes (number, date, string, boolean) can only used for read operation only (not write operation), because ForEach will create a copy of data, not the actual data, just as shown below :

Please Login or Register  to view this content.

Here is a formula solution that may work for you
Enter formula in B1 and copy across and down

Formula:

Please Login or Register  to view this content.

v	A	B	C	D	E	F	G	H	I
1	abc,cde:def;ghi	abc	cde	def	ghi	jkl	mno	pqr	stu
2	jkl.mno>pqr^stu	vwx	yza	bcd	efg	hij	klm	nop	qrs
3	vwx,yza:bcd;efg	rrr	bbb	sss	ggg	opp	nno	lnb	pkk
4	hij.klm>nop^qrs
5	rrr.bbb.sss;ggg
6	opp.nno,lnb,pkk

@sir Alkey

Appreciated the formula. I have kept it if I plan to avoid a VBA code in future.

But currently I have large number of txt files and the vba solution is applied as it will also copy the data from txt files. Many thanks indeed.