How to split text file by character spaces...

[kkmoslephour]

Hi all,

I am trying to read in a text file and split it by character spaces and output to excel sheet, right now I can read in the text file, but I am having problem splitting it into an array by character spaces. Here is my vba code:

Sub ParseText()




Dim myFile As String, text As String, textline As String


myFile = Application.GetOpenFilename("Text Files (*.txt), *.txt")
MsgBox myFile
If myFile <> "False" Then
    MsgBox "Opening " & myFile
End If




Open myFile For Input As #1


Do Until EOF(1)
    Line Input #1, textline
    MsgBox textline
    text = text & textline
    MsgBox text
Loop


Close #1


End Sub

below is a portion of my text file (as you can see the text file spacing isn't consistent)

DESCRIPTION C.QTY R.QTY H/W P/N SW Ver SW P/N CONFIG
==============================================================================
44 XXX XXX XXX 2 2 XX-XX8888-88 01.25.00.7MEI4B-8888-8888888888E
44 YYY YYYYYY YYY 2 NULL 01.05.00.7MEI4D-9999-4001NULL
44 ZZZ ZZZZ ZZZZ ZZZZ000 00 NULL 01.02.00.7MEI00-0000-0000NULL

 

[Stildawn]

Not sure what you are trying to do, but excel can open txt files directly, each line will be one massive merged cell however.

After opening in Excel, you could do text to columns with a delimiter of a " " to separate out each space into a column of its own.

Alternatively if you want to use Arrays on each line in VBA then you can use:

LineArray = Split(Range("A1").Value," ")

Which would create a Array variable in LineArray with all words split out by a space.

Hope this helps

 

[kkmoslephour]

Im sorry, but I dont think you got my question correctly. I am trying to split by a number of spaces, lets say I want 7 character spaces inputted in my first cell, e,g "hot dog" contains 7 spaces (including the white space), I want that the be inside in my A1 cell, and then, lets say, 10 spaces for my B1 cell. You get what I am saying?

 

[kkmoslephour]

so for my text file example "44 XXX XXX XXX" I want that to be in my A1 cell, and then "2" in my B1 cell, and then "2" in my C1 cell ... etc.

 

[Stildawn]

Ah ok, is the number of characters always consistent?

I would think you would have to do a VBA loop on the lines using a series of steps for each line, something like this:

Sub Splitlines()


Lastrow = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To Lastrow 'this sets the rows to go through, if row 1 is a head then maybe start at row 2
    Range("B" & i) = Left(Range("A" & i).Value, 7) 'the number 7 in here is the number of characters from Left
    Range("C" & i) = Mid(Range("A" & i).Value, 8, 10) 'the number 8 is where you want to start (1 after 7 in the step previously) and 10 is the length
    Range("D" & i) = Mid(Range("A" & i).Value, 19, 6) '19 is 8+10+1 the place to start and 6 would be your next length
    'keep doing as many of these as required using the character lengths you have
    Range("A:A").Delete 'this deletes the orginal unsplit column which has now been split out
Next i


End Sub

 

[AlphaFrog]

The Text to Columns feature previously mentioned does have a Fixed Width mode where each column split can have a constant split.

 

[Stildawn]

I always find the fixed width mode terrible lol. But yes its worth a try for sure.

 

[kkmoslephour]

Okay thanks let me try it and let you know, also I notice when I output the textline using MsgBox in my code, I dont see my whole text file info being outputted into the textbox, Is there a buffer space limit or something, thats why its only showing partial of my text file?

 

[kkmoslephour]

Ah ok, is the number of characters always consistent?

I would think you would have to do a VBA loop on the lines using a series of steps for each line, something like this:

Sub Splitlines()


Lastrow = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To Lastrow 'this sets the rows to go through, if row 1 is a head then maybe start at row 2
    Range("B" & i) = Left(Range("A" & i).Value, 7) 'the number 7 in here is the number of characters from Left
    Range("C" & i) = Mid(Range("A" & i).Value, 8, 10) 'the number 8 is where you want to start (1 after 7 in the step previously) and 10 is the length
    Range("D" & i) = Mid(Range("A" & i).Value, 19, 6) '19 is 8+10+1 the place to start and 6 would be your next length
    'keep doing as many of these as required using the character lengths you have
    Range("A:A").Delete 'this deletes the orginal unsplit column which has now been split out
Next i


End Sub

Hi I tried this method, I dont quite get how the lastRow is being calculated, when I output last row for the first time its 57, after second run lastRow is 1. I am suspecting the lastRow became one because I have the my whole textfile being stored in my 'textline' array therefor it is one big array?

 

[Stildawn]

Lastrow in the above is simply getting the row number of the last used row in Column A.

So it works if all your textfile lines are in Column A, which should happen naturally when opening a txt file in Excel.