Split String with Spaces

  • I previously used the split string function many times on a text file dump.
    Now it seems to not be able to split the line correctly.
    It seems to be something in the text file dump but can't figure it out.


    First Text File, line 1
    1 $37.35 $24.26 9.25/


    Second Text File, line 1
    1 $22.35 $14.53 3.94/


    I even put the first text file line into the second text file line and it works but the original does not
    My code is simply reading in each line of a file then splitting it into an array.


    Code
    Line Input #FileNum, DataLine
    MyInfo = Split(DataLine, " ")
    ActiveWorkbook.Worksheets("TestArea").Cells(j + 4, 8) = Trim(MyInfo(0))
    ActiveWorkbook.Worksheets("TestArea").Cells(j + 4, 9) = Trim(MyInfo(1))



    Results using First text file is Myinfo(0) = " 1" MyInfo (1) = " $37.35" and so on
    Results using Second text file is Myinfo(0) = " 1 $22.35 $14.53 3.94/" and MyInfo(1) is out of range


    I have also tried MyInfo = Split(DataLine) as I know the space is default delimiter.
    I am truly baffled as to cause.


    FIrst time poster, hope I did this right!

  • Re: Split String with Spaces


    Hi gfabyan,


    Welcome to the Ozgrid forum.


    Almost correct with the code tags, but you need to put square brackets [] around 'code' and '/code'. Try editing your post to add these, and you should see the VBA code appear correctly.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: Split String with Spaces


    I suspect that the data in the file is delimited by something other than a space.


    Try stopping the program and putting something similar to this in the Immediate Pane:
    ? Mid(Myinfo(0),3,1)=Chr(32)


    If it is a space that should return TRUE. If it's a different non-visible character it will return FALSE.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: Split String with Spaces


    Thank you Batman. You got me going the right direction. Found out it was a ASCII Character 9 Horizontal Tab


    solved it with this line


    Code
    DataLine = Replace(DataLine, Chr(9), Chr(32))


    Quote from Batman;766735

    Hi gfabyan,


    Welcome to the Ozgrid forum.


    Almost correct with the code tags, but you need to put square brackets [] around 'code' and '/code'. Try editing your post to add these, and you should see the VBA code appear correctly.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!