VBA Split Function to Replicate Text To Columns

[xljunkie]

I start off with a comma delimited text file which I import into a table. One of these columns is further space (or tab?) delimited. I'm more used to Excel and since I don't have SEARCH or FIND functions available to me in a query field, I created my own function in VBA to do text to columns:

Function SplitField(strValue As String, strDelimiter As String, intPartWanted As Integer) As String
    SplitField = Split(strValue, strDelimiter, , vbTextCompare)(intPartWanted - 1)
End Function

I can now pull in the first column correctly with Hours: SplitField([HoursUnconverted]," ",1). However, the number of spaces between each actual string of text differs. I might have this in my table (imagine the periods are spaces):

7:00AM-5:00PM.......CLOSED.......CLOSED
7:00AM-5:00PM.......CLOSED.......CLOSED
CLOSED..................CLOSED.......CLOSED

I'm having trouble consistently getting the 2nd and 3rd text strings above because of the number of spaces. Any help? I can explain in a different way if necessary.

 

[xljunkie]

Figured out a way that will work for me. It isn't as flexible as I'd like but first apply this formula on a query: HoursStep1: Replace(Replace(Replace(Replace([Hoursunconverted]," "," ")," "," ")," "," ")," "," ")

This is just slowly replacing double spaces down to one space. It works for my dataset but could potentially not work for others. Then I can use the function I created to do a split by space delimiter.