Split with consecutive delimiters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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.
 
Ignore my previous post. TRIM is available to VBA:

Sub single_spaces()
Selection.Value = Application.WorksheetFunction.Trim(Selection.Value)
End Sub
 
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?
Click to expand...

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
 
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
 
Back
Top