Long txt data import in cell

[geodesy]

Hi there,

May I ask for your expertise of how to import a long data into excel
Normally I can import a txt data, "text to column" it and it will b
laid out into each column nicely. But the data for this file is to
long (about 500 column needed) that it would not fit into one row
Therefore, I opted for all data separated into each cell fitting int
one column instead. Can anyone teach me how to transpose this?

When I copy the file and try to paste special or transpose it, I don'
get the option to transpose it. Please help.

Any advice appreciated. Thank you in advance.

Cheers,
Ale

 

[Bernie Deitrick]

Alex,

Below is a macro that will traspose during import. Note that you are now
limited to 256 rows of imported data...

HTH,
Bernie
MS Excel MVP

Sub ImportMoreThan256ColumnFile()
TransposeImportTextFile "C:\Excel\WideTest.txt", " "
End Sub

Public Sub TransposeImportTextFile(FName As String, Sep As String)
'Based on Chip Pearson's Code
'Modified by Bernie Deitrick June 10, 2003
Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveRowNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveRowNdx = ActiveCell.Row
ColNdx = ActiveCell.Column

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) <> Sep Then
WholeLine = WholeLine & Sep
End If
RowNdx = SaveRowNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos >= 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
RowNdx = RowNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
ColNdx = ColNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub

 

[geodesy]

Hi Bernie,

Thanks for the help. But alas, I am a normal user and do not know ho
to apply these into use. What's the step?

Cheers,
Ale

 

[Bernie Deitrick]

Alex,

Copy the code. Go into Excel, open a new file, and save it with a name that
you will recognize. Then press Alt-F11 to open the Visual Basic Editor
(VBE). Press Ctrl-R to open the project explorer, then click on the file
with tha name that you just saved.

From the main menu of the VBE select Insert then choose module. In the big
window to the right, paste the code using Ctrl-V.

Then change the "C:\Excel\WideTest.txt", " " part of the macro below to
reflect the file name and path of the file you want to open, and what
character you want to use as the delimiter: this uses a space.

Sub ImportMoreThan256ColumnFile()
TransposeImportTextFile "C:\Excel\WideTest.txt", " "
End Sub

Then put your cursor somewhere inside that, and press F5 to run it.

HTH,
Bernie
MS Excel MVP