Hey all,

I need to format a text file that has about 2500 entries, set up in 3
fields like this:

111.22.33.44 machinename # a coment goes here

This file has gotten updated over the years, but the field delimiters
havent been kept constant: some are single tabs (good), others are
several spaces/tabs (bad). If I could format the file in such a way
that the 3 fields are separated by a single tab, that would make
importing the file into Excel far cleaner.

I've thought about adding a method to my macro that goes through the
text file and replaces all spaces with a single tab, but unfortunately
this would also be done to the comments, which I can't have happen. All
spaces, and multiple tabs separating the 3 fields need to be replaced
with a single tab, and anything after the '#' sign needs to be left as
is.

Is there a way that this delimiter formatting can happen after
importing into excel, or is it something that needs to be done into a
temp file before importing? Any and all help is appreciated.

Thanks in advance,

Roman

Hi Roman,

Import into Excel as it is, all in one column.
Then DataText to columns, with # as the delimiter. Now your comment is safe.
Then insert enough blank columns and do another DataText to columns, check all possible delimiters and check "Treat consecutive
delimiters as one"

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"RomanR" wrote in message ups.com...
| Hey all,
|
| I need to format a text file that has about 2500 entries, set up in 3
| fields like this:
|
| 111.22.33.44 machinename # a coment goes here
|
| This file has gotten updated over the years, but the field delimiters
| havent been kept constant: some are single tabs (good), others are
| several spaces/tabs (bad). If I could format the file in such a way
| that the 3 fields are separated by a single tab, that would make
| importing the file into Excel far cleaner.
|
| I've thought about adding a method to my macro that goes through the
| text file and replaces all spaces with a single tab, but unfortunately
| this would also be done to the comments, which I can't have happen. All
| spaces, and multiple tabs separating the 3 fields need to be replaced
| with a single tab, and anything after the '#' sign needs to be left as
| is.
|
| Is there a way that this delimiter formatting can happen after
| importing into excel, or is it something that needs to be done into a
| temp file before importing? Any and all help is appreciated.
|
| Thanks in advance,
|
| Roman
|

When you import the data into Excel (DataGet external DataImport Text), as
well as setting the delimiter, there is an option to treat multiple
delimiters as one.
So that should get the 3 columns into Excel. You can then use TRIM to remove
excess spaces.
Then .SaveAs a tab delimted file.
Would that work ?

NickHk

"RomanR"
groups.com...
Hey all,

I need to format a text file that has about 2500 entries, set up in 3
fields like this:

111.22.33.44 machinename # a coment goes here

This file has gotten updated over the years, but the field delimiters
havent been kept constant: some are single tabs (good), others are
several spaces/tabs (bad). If I could format the file in such a way
that the 3 fields are separated by a single tab, that would make
importing the file into Excel far cleaner.

I've thought about adding a method to my macro that goes through the
text file and replaces all spaces with a single tab, but unfortunately
this would also be done to the comments, which I can't have happen. All
spaces, and multiple tabs separating the 3 fields need to be replaced
with a single tab, and anything after the '#' sign needs to be left as
is.

Is there a way that this delimiter formatting can happen after
importing into excel, or is it something that needs to be done into a
temp file before importing? Any and all help is appreciated.

Thanks in advance,

Roman

Sub CleanFile()
Dim s1 As String, s2 As String
Dim iloc As Long
Dim SourceNum As Integer
Dim DestNum As Integer
Dim Temp As String

' If an error occurs, close the files and end the macro.
On Error GoTo ErrHandler

' Open the destination text file.

DestNum = FreeFile()
Open "C:\Data\DEST.TXT" For Output As DestNum

' Open the source text file.
SourceNum = FreeFile()
Open "C:\Data\SOURCE.TXT" For Input As SourceNum


' Read each line of the source file.
' clean it up and write it to the
' destination file.
Do While Not EOF(SourceNum)
Line Input #SourceNum, Temp
iloc = InStr(1, Temp, "#", vbTextCompare)
If iloc < 0 Then
s1 = Left(Temp, iloc - 1)
s2 = Right(Temp, Len(Temp) - iloc)
Else
s1 = Temp
s2 = ""
End If
s1 = Application.Trim(s1)
s1 = Replace(s1, " ", vbTab)
Do While InStr(1, s1, vbTab & vbTab, vbTextCompare) 0
s1 = Replace(s1, vbTab & vbTab, vbTab)
Loop
If Len(Trim(s2)) 0 Then
Print #DestNum, s1 & "#", s2
Else
Print #DestNum, s1
End If
Loop

CloseFiles:

' Close the destination file and the source file.
Close #DestNum
Close #SourceNum
Exit Sub

ErrHandler:

MsgBox "Error # " & Err & ": " & Error(Err)
Resume CloseFiles

End Sub

--
Regards,
Tom Ogilvy


"RomanR" wrote:

Hey all,

I need to format a text file that has about 2500 entries, set up in 3
fields like this:

111.22.33.44 machinename # a coment goes here

This file has gotten updated over the years, but the field delimiters
havent been kept constant: some are single tabs (good), others are
several spaces/tabs (bad). If I could format the file in such a way
that the 3 fields are separated by a single tab, that would make
importing the file into Excel far cleaner.

I've thought about adding a method to my macro that goes through the
text file and replaces all spaces with a single tab, but unfortunately
this would also be done to the comments, which I can't have happen. All
spaces, and multiple tabs separating the 3 fields need to be replaced
with a single tab, and anything after the '#' sign needs to be left as
is.

Is there a way that this delimiter formatting can happen after
importing into excel, or is it something that needs to be done into a
temp file before importing? Any and all help is appreciated.

Thanks in advance,

Roman