Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split with consecutive delimiters
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split with consecutive delimiters
Do you mean that you actually have a fixed-width format file ?
NickHK "simonc" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split with consecutive delimiters
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 - gsnu200721 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split with consecutive delimiters
Ignore my previous post. TRIM is available to VBA:
Sub single_spaces() Selection.Value = Application.WorksheetFunction.Trim(Selection.Value ) End Sub -- Gary''s Student - gsnu200721 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split with consecutive delimiters
On Wed, 16 May 2007 02:21:02 -0700, simonc
wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split with consecutive delimiters
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 "simonc" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Delimiters | New Users to Excel | |||
Multiple Delimiters | Excel Discussion (Misc queries) | |||
Consecutive date range on consecutive worksheets | Excel Worksheet Functions | |||
delimiters and manipulation | Excel Worksheet Functions | |||
How Do I Modify Pearson's Code to Add "Treat Consecutive Delimiters As One"? | Excel Programming |