Split an input line from text file

[KenCriss]

I know how to read a text file from VBA and use the split function, but in this case, I want to take a portion of that input line to create a new line. For example, if this were my input line
4346123456789JOHN DOE 13154862 2

How could I write data out to another file such that I would have this:
4346123456789JOHN 13154862 2
4346123456789 DOE

I am basically trying to split the data but based on a certain numbered column in the file, not on a delimiter and move some data to a separate line when writing the data back out. Ideally I want to just write it back out to another text file, but I could put it into a worksheet also.

Do I need to do something like this

Line Input #1, tmp

and then take "tmp" and try to split it for a certain number of chars?

 

[ChrisM]

You already have all the tools in place with the split function. Since Split returns an array, just build your second data line with the appropriate array indexes.

So, if you split like this:

yourArray = Split(blah blah blah)

Then your new second line of data would be like this:

strData = yourArray(0) & " " & yourArray(1) & " " & yourArray(2)

Just replace 0,1,2 with whatever "columns" you're looking for.

 

[KenCriss]

Thanks. That would be great except Split wants a delimeter and if you don't give it one, it uses a " ". In my particular case, I need to pick out columns 59-66 of the text file. If I let Split delimit the input line by " ", the output I want might be in yourarray(2) or yourarray(3) or yourarray(?). Sorry, I probably asked the wrong question to begin with.

 

[ChrisM]

I'm still not following. You talk about not using a delimiter, but at the same time looking at certain numbered columns. The only way to arrive at a particular column is to delimit your data into columns!

What is stopping you from using yourArray(58) thru (65) to get at your columns 59 thru 66?

Is the column count of your raw string changing from line to line? Say line 1 has 65 columns and line 2 only has 44?

 

[KenCriss]

Okay. Hypothetical example
Data in the text file:

4346123456789JOHN DOE 13154862 2
4346987654321Jim Doe Jr. 54564546 2

I need to extract 13154862 for John Doe and 54564546 for Jim Doe Jr.

If I do this

        Line Input #1, tmp
        myarray = Split(tmp)

Then won't John's array be
myarray(0) = "4346123456789JOHN"
myarray(1) = "DOE"
myarray(2) = "13154862"
myarray(3) = "2"

and Jim's array be
myarray(0) = "4346123456789Jim"
myarray(1) = "Doe"
myarray(2) = "Jr."
myarray(3) = "54564546"
myarray(4) = "2"

So that the item I want is in array(2) for one person and array(3) for another person. I guess what I am saying is that it would be in a different spot in the array for every person because the number of spaces in the file will be different for every line in the file.

 

[KenCriss]

Hey, I got it by splitting it on Chr(13), then taking what I want out of it

Line Input #1, tmp
        myarray = Split(tmp, Chr(13))
        strdata = Left(myarray(0), 58)