Special Characters when Opening Text Files

  • Thread starter Thread starter bchan
  • Start date Start date
B

bchan

When I open a text file which is delimited with tab and semi-colon, th
TAB control character (represented by a square box) appears along wit
the data. If I choose the Tab and Semi-colon delimiters, the TA
control character disappears but the columns spill over to the nex
column and is no longer aligned with the header. If I do not choos
the Tab (just the semi-colon), the columns will align properly but th
square boxes appear.

However, when I open the same file from another computer, I don'
encounter this problem. I checked out the Options and everything look
the same. In my Options settings, under the International tab, I hav
unselected the option Show Control Characters. Still no luck.

Can anybody help me out here
 
I wonder if the file has form-feed (character 0010) or hard-space (character
0160) next to the tab character. See if you can find FxEdit on the net
(Google for it). This will show all characters in hexadecimal notation
best wishes
Bernard
 
It kind of sounds like you don't want your data delimited by tabs--you just want
it delimited by the semicolon.

If that's true, then you should know that excel doesn't really allow tabs within
the cell--like a cell in an MSWord table does.

But if you import your text file, you can use a macro to change those tab
characters to something else (space or multiple spaces???).


Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(9))

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), Replacement:=" ", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Next iCtr

End Sub

(I changed the tab characters to " ".)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top