Importing Text File to Excel

  • Thread starter Thread starter Matthew MacCarthy
  • Start date Start date
M

Matthew MacCarthy

How is it possible to import data from a text file into
Excel so that each data element is contained in its own
cell, instead of an entire line of the text data being in
one cell?
 
When I open a text file (like *.txt), I get a wizard that'll help me parse each
line into separate cells in each row.

If each element (not sure what that means) is separated by a space character,
I'd select delimited when that wizard asked me to choose between "fixed width"
and "delimited". Then the next step prompts me for the delimiter.

This is pretty standard. But something else must be happening for you?????
 
Matthew

You should be able to open Excel and File>Open.

Browse to your Text file and double-click or "open".

The Text to Columns Wizard will open and give you importing options.

i.e. "de-limited"...you choose the de-limiter
"fixed width....you choose where to enter breaks

If you have many to do, I suggest recording a macro while opening the first
then assign the macro to a button or shortcut key combo.

Gord Dibben XL2002
 
If it was a .csv file, it will open without the wizard. To separate the
elements, select the first column, and choose Data>Text to Columns. Then
follow the steps that Dave described.
 
sometimes when you open a txt file the wizard does not
appear to parse it. if that is the case try this:
1.select the entire column
2. goto tools>text to column
3. the wizard will now appear and you prob want to choose
deliminate. you will have to choose tab, space etc for
delimination. this depends on the data. If you don't like
the results undo and do it again.
 
I am trying to take info that copies into a column to convert to a row

When i copy an address from the webpage it displays like this

Name
1234 Address Street
Phone(123) 345-9876

I want it to read like this (as a row, each data in differen
columns):

Name | 1234 Address Street | Phone(123) 345-9876

-dou
 
I use Microsoft word macro to do what you want. You don't need to know
any visual basic code to create the macro, merely record the required
keystokes.

1 - copy data into an MSword document or other word processor with
similar capability.

2 - using global substitution, substitute a unique character sequence
for the double record return at the end of each data sequence.
Alternatively, if there is not a blank record between each data
sequence, clue on a something in the record to insert a marker. For
example, if the data record is of the form: "name = Joe Smith" , do a
global substitution:

find: "name"
substitute "^p^pname"

you'll then have a blank line above each line where the term "name"
appears.
Then substitute the marker. I use !@#$%

find: ^p^p
substitute: !@#$%

3) Substitute a tab for each paragraph / record return.

find: ^p
substitute: ^t

4) remove markers and replace with original paragraph / record return

find: !@#$%
substitute: ^p

5) copy data from MSword an paste into a column in Excel

6) parse (TEXT to COLUMNS) the columns based on TAB delineator.


Good Luck .... windsurfer in LA
 
doug

If the data is consistent in sets of 3, run this macro and enter 3 in the
inputbox. Also assumes data is in Column A.


Sub ColtoRows()
Dim rng As Range
Dim i As Long
Dim j As Long
Dim nocols As Long
Set rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error Resume Next
nocols = InputBox("Enter Number of Columns Desired")

For i = 1 To rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(rng.Row, "A")).ClearContents

End Sub

Gord Dibben Excel MVP
 
Back
Top