View Single Post
  #14   Report Post  
Old January 3rd 19, 12:25 PM posted to microsoft.public.excel.programming
GS[_6_] GS[_6_] is offline
external usenet poster
First recorded activity by ExcelBanter: Apr 2015
Posts: 977
Default covert data from text file into columns


Am Wed, 2 Jan 2019 10:04:26 -0800 (PST) schrieb :

I tried Claus solution but that did not work. Yours did.

download the workbook from here and test it (Macros are disabled in
In Sheet1 you see your new data, in sheet2 the previous data.

Claus B.

FSO needs to be destroyed when you're finished with it so its memory space gets
released. Each instance you create stays in memory until it's destroyed and so
'programmer best practice' suggests that any code that deliberately creates an
object should also deliberately 'Set objVarName = Nothing' when no longer

The downside to the nature of this task is the 1st line of the blocks of text
varies file-to-file. This needs to be able to pull the block delimiter text and
headers from the file rather than these be hard-coded.

Thanks for the suggestion to 'clean' the text for unwanted chars.
Unfortunately, WorksheetFunction.Clean doesn't include any non-printable chars
above 32 so I made my own function that specifies chars to keep and include

Function FilterString$(ByVal TextIn$, Optional IncludeChars$, _
Optional IncludeLetters As Boolean = True, _
Optional IncludeNumbers As Boolean = True)
' Filters out all unwanted characters in a string.
' Arguments: TextIn The string being filtered.
' IncludeChars [Optional] Any non alpha-numeric characters to
' IncludeLetters [Optional] Keeps any letters.
' IncludeNumbers [Optional] Keeps any numbers.
' Returns: String containing only wanted characters.
' Comments: Works very fast using the Mid$() function over other methods.

Const sSource$ = "FilterString()"

'The basic characters to always keep by default
Const sLetters As String = "abcdefghijklmnopqrstuvwxyz"
Const sNumbers As String = "0123456789"

Dim i&, sKeepers$

sKeepers = IncludeChars
If IncludeLetters Then _
sKeepers = sKeepers & sLetters & UCase(sLetters)
If IncludeNumbers Then sKeepers = sKeepers & sNumbers

For i = 1 To Len(TextIn)
If InStr(sKeepers, Mid$(TextIn, i, 1)) Then _
FilterString = FilterString & Mid$(TextIn, i, 1)
End Function 'FilterString()


Free usenet access at
Classic VB Users Regroup!