Split with consecutive delimiters

[simonc]

I am reading lines from a formatted data file (values in right justified
columns) and using split to extract the values from each line into an array.
The problem is the number of spaces between values is not constant, and split
adds a new array value for every space it finds in the text string. Is there
a way to set split to treat consecutive spaces as a single delimiter like you
can in TextToColumns?

 

[NickHK]

Do you mean that you actually have a fixed-width format file ?

NickHK

 

[Gary''s Student]

The following removes multiple spaces from a cell:

Sub break_up()
''''''''''''''''''''''''''''''''''''''''''''
' converts multiple spaces to a single space
''''''''''''''''''''''''''''''''''''''''''''
Dim v As String
Dim v_out As String
v = Selection.Value
v_out = ""
c = 0
For i = 1 To Len(v)
vv = Mid(v, i, 1)
If vv = " " Then
c = c + 1
Else
Select Case c
Case 0
v_out = v_out & vv
Case 1
v_out = v_out & " " & vv
Case Is > 1
v_out = v_out & " " & vv
End Select
c = 0
End If
Next
Selection.Value = v_out
End Sub

you can adapt the logic to remove multiple spaces from your string before
"splitting" it.

 

[Gary''s Student]

Ignore my previous post. TRIM is available to VBA:

Sub single_spaces()
Selection.Value = Application.WorksheetFunction.Trim(Selection.Value)
End Sub

 

[Ron Rosenfeld]

I am reading lines from a formatted data file (values in right justified
columns) and using split to extract the values from each line into an array.
The problem is the number of spaces between values is not constant, and split
adds a new array value for every space it finds in the text string. Is there
a way to set split to treat consecutive spaces as a single delimiter like you
can in TextToColumns?

No but you could remove the extraneous spaces by using
application.worksheetfunction.trim on the string. (Do NOT use the VBA Trim
function -- it only removes leading and trailing spaces).


--ron

 

[Tim]

Split seesm like the wrong approach.
What if there are empty values in the data ?

You might be better off creating a function which returns a specified field
from the line (and that can also trim off any leading spaces).
How you would construct this would depend on how many fields there are, but
a simple select case would probably be fine

(untested)

Function GetField(fnum as integer,sLine as string)
dim rv as string
select case fnum
case 1: rv=left(sLine,10)
case 2:rv=mid(sline,11,10)
case 3:rv=mid(sline,21,15)
'...etc
case else: rv=""
end select

GetField=trim(rv)

end function


Tim