Split an input line from text file

KenCriss

Active Member
Joined
Jun 6, 2005
Messages
323
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
Code:
Line Input #1, tmp
and then take "tmp" and try to split it for a certain number of chars?
 

ChrisM

Well-known Member
Joined
Jun 11, 2002
Messages
2,128
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

Active Member
Joined
Jun 6, 2005
Messages
323
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

Well-known Member
Joined
Jun 11, 2002
Messages
2,128
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

Active Member
Joined
Jun 6, 2005
Messages
323
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
Code:
        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

Active Member
Joined
Jun 6, 2005
Messages
323
Hey, I got it by splitting it on Chr(13), then taking what I want out of it

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

Forum statistics

Threads
1,077,835
Messages
5,336,664
Members
399,095
Latest member
globz

Some videos you may like

This Week's Hot Topics

Top