Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing a space-delimited txt. file right-justified into Excel
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing a space-delimited txt. file right-justified into Excel
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing a space-delimited txt. file right-justified into Excel
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. -- Thanks, Shane Devenshire "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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dollar sign left justified - data right justified - large space | Excel Discussion (Misc queries) | |||
Output space-delimited file with quotes around text? | Excel Discussion (Misc queries) | |||
Importing Comma delimited file | Excel Discussion (Misc queries) | |||
Parsing a space delimited file into segments | Excel Discussion (Misc queries) | |||
HOW DO I EDIT AN EXISTING FILE TO BE SPACE DELIMITED? | Excel Discussion (Misc queries) |