Importing a space-delimited txt. file right-justified into Excel

[Mohavedad]

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]

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 here:
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.)

 

[ShaneDevenshire]

Hi,

1. I used the Import Wizard on your data choosing Delimited, with a Space
as the delimiter. All the numbers came in right justified, but it sounds
like your's don't?
2. One way that might convert all the text numbers to numbers would be:
a. Select an empty cell and choose Copy, select all the data and choose
Edit, Paste Special, Add.
b. Or, you might try Ctrl+H, with all the data selected and type a space
(hit spacebar once) in the Find what box and nothing in the Replace with box.
Then click Replace All
3. To deal with the names - Since the name may occupy varing number of
columns, select ALL the resulting output and
Press F5, Special, Constants, and turn off all the options except Text and
click OK.
Press Ctrl+- (Ctrl and the Minus key) and choose Shift cells left.
Remove any unnecesary 0's to the right of your data.