Novice needs Array help.

[Guest]

I have a specialized program that exports data in a horrible text format,
that is some what consistent.

I need to take the records of each line and split them out into columns. I
am using a varString = Split(junkfield, ,6) - This is as far as I have gone
so far. What is happening is that when I use the blank spaces and then
assign the varString to field values I get some blank values. This is
because there are some records that have two or more spaces between data.
Did I make sense or is more information required?

I need to know how to weed out the extra spaces so that I can bunch up the
data together.

Thanks,

-Steve

 

[Dirk Goldgar]

I have a specialized program that exports data in a horrible text
format, that is some what consistent.

I need to take the records of each line and split them out into
columns. I am using a varString = Split(junkfield, ,6) - This is as
far as I have gone so far. What is happening is that when I use the
blank spaces and then assign the varString to field values I get some
blank values. This is because there are some records that have two
or more spaces between data. Did I make sense or is more information
required?

I need to know how to weed out the extra spaces so that I can bunch
up the data together.

Thanks,

-Steve

Here's a function you can paste into a standard module and use in place
of the builtin Split() function for cases like this:

'----- start of code -----
Function SplitWords( _
ByVal pstrSource As String, _
Optional pstrDelim As String = " ", _
Optional plngLimit As Long = -1, _
Optional pCompare As Long = vbBinaryCompare) _
As String()

' Split the argument pstrSource into an array of "words"
' based on the delimiter passed as pstrDelim, treating
' consecutive occurrences of the delimiter as a single
' occurrence. So, for example, SplitWords("This Function")
' returns the same output array as SplitWords("This Function").
'
' Written by: Dirk Goldgar
' on: 8 March, 2005

Dim strTwoDelim As String
Dim lngLen As Long

strTwoDelim = pstrDelim & pstrDelim

Do
lngLen = Len(pstrSource)
pstrSource = Replace(pstrSource, strTwoDelim, pstrDelim, 1, -1,
pCompare)
Loop Until Len(pstrSource) = lngLen

SplitWords = Split(pstrSource, pstrDelim, plngLimit, pCompare)

End Function
'----- end of code -----

I just threw it together, but it seems to work.

 

[Guest]

Thanks Dirk, it is perfect...

Here's a function you can paste into a standard module and use in place
of the builtin Split() function for cases like this:

'----- start of code -----
Function SplitWords( _
ByVal pstrSource As String, _
Optional pstrDelim As String = " ", _
Optional plngLimit As Long = -1, _
Optional pCompare As Long = vbBinaryCompare) _
As String()

' Split the argument pstrSource into an array of "words"
' based on the delimiter passed as pstrDelim, treating
' consecutive occurrences of the delimiter as a single
' occurrence. So, for example, SplitWords("This Function")
' returns the same output array as SplitWords("This Function").
'
' Written by: Dirk Goldgar
' on: 8 March, 2005

Dim strTwoDelim As String
Dim lngLen As Long

strTwoDelim = pstrDelim & pstrDelim

Do
lngLen = Len(pstrSource)
pstrSource = Replace(pstrSource, strTwoDelim, pstrDelim, 1, -1,
pCompare)
Loop Until Len(pstrSource) = lngLen

SplitWords = Split(pstrSource, pstrDelim, plngLimit, pCompare)

End Function
'----- end of code -----

I just threw it together, but it seems to work.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)