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,129
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,129
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,081,795
Messages
5,361,333
Members
400,627
Latest member
Mcomeaux

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top