I would bring the data into column A and then run a macro to extract the last 4
entries in the line:
Option Explicit
Sub testme()
Dim myCell As Range
Dim myRng As Range
Dim mySplit As Variant
Dim iCtr As Long
Dim cCtr As Long
With ActiveSheet
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
For Each myCell In myRng.Cells
mySplit = Split(Application.Trim(myCell.Value), " ")
If (UBound(mySplit) - LBound(mySplit) + 1) < 4 Then
MsgBox "not enough pieces for row #: " & myCell.Row
Else
cCtr = 0
For iCtr = UBound(mySplit) - 3 To UBound(mySplit)
cCtr = cCtr + 1
If IsNumeric(mySplit(iCtr)) Then
myCell.Offset(0, cCtr).Value = mySplit(iCtr)
Else
myCell.Offset(0, cCtr).Value = "Error!"
End If
Next iCtr
End If
Next myCell
.Columns(1).Delete
End With
End Sub
If you're new to macros:
Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html
David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm
(General, Regular and Standard modules all describe the same thing.)
Mohavedad wrote:
I have a space-delimited txt. file, whose cells need to be imported right
justified into Excel. The txt. file looks like this:
Barney Fife Mayberry 20.00 15.00 10.00 55.00
Thomas Newark 15.00 10.00 5.00 30.00
Mabel Mattingly Louisville Kentucky 5.00 10.00 5.00 20.00
The names in the example above are not being used, but I do need the 4
columns on the right. I've been using the txt import wizard in Excel 2002,
but it is formatted to import the data left-justified, so i have to manually
fix each line.
Does anyone have any suggestions?
--
Dave Peterson