Text File

K

Kreiss

I have a large text file that the fields are seperated by
returns. When importing a txt file there is a tab
for "Other" when asking what kind of field delimeter, but
I don't know how to specify a return. Below is a couple
of examples of the records I need put in columns. What I
eventually need done is a mail merge

Thanks,
Kreiss

11Mike L Fox
140261 N Virginia Drivee
1Antioch IL 60002
12ABC
1
11Nicholas C Know
1246 Summertown Dr Ave
1Antioch IL 60002
12ABC
1
 
J

jeff

Hi,

I think if you just uncheck everything, it'll import
ok (I tried the old standby ctrl+M, but it's not taken)

Others here may have the definitive answer.

jeff
 
G

Gord Dibben

Kreiss

The line feeds could be either 0010 or 0013.

Edit>Replace.

replace what: ALT + 0010(on number pad)
replace with: nothing

You can use the same ALT + 0010 or 0013 in the "other" box in Text to Columns.

Macro........

Sub ReplaceCRLF()
'Bill Manville Thu, 4 Feb 1999
Selection.Replace Chr(13)," ",xlPart 'x1Part apply to within cells
(default)
Selection.Replace Chr(10)," ",xlPart 'xlWhole apply to entire cell
content
End Sub


You also may have the 0160 character. Use David McRitchie's TRIMALL macro.

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Gord Dibben Excel MVP
 
Top