.txt into columns

[Sunefar]

Hi.

I have a .txt file :

Anders Andersen
Andelsvej 10
1234 Andeby.

Anette Andersen
Sovej 2
3345 Greve.

...e.c.t

I would like to import into a .XLS file and convert it into colums like this :

Colum 1 |Colum 2 | Colum 3 | Colum 4 | Colum 5 |
Anders | Andersen | Andelsvej 10 | 1234 | Andeby.|
Anette | Andersen | Andelsvej 11 | 1234 | Andeby. |

Can this be done?

If not is ther a way to do it with a .xls file like this (I also have that file) :

1|Anders Andersen
2|Andelsvej 10
3|1234 Andeby.
4|
5|Anette Andersen
6|Sovej 2
7|3345 Greve.
8|
9|..e.c.t

Into the same colum's or is it cells...

I am using "Microsoft Office Excel 2007"
It's for a adress database.

Thanks for your help.

 

[Sunefar]

I know that I can copy my text file and paste into Excel so that each line drop into a seperate cell.

If I have row 1,2,3,with text then 4 blank, 5,6,7 text, 8 blank, I can do this in B1

=A1&" "&A2&" "&A3

and this in B5

=A5&" "&A6&" "&A7

It looks like this :

But where do I go from ther?? Is ther a way to "auto-fill" down??

Are ther a "Macro" I can use to do the job?

I am greatfull for all help - Thanks!!

 

[ColinKJ]

Hi Sunefar,

Where are your .txt file stored?

ColinKJ

 

[Sunefar]

Here : C:\Documents and Settings\Sune\Skrivebord\list.txt

"Skrivebord" = Desktop

Why?

 

[alansidman]

Here is an extract that I had received a while ago that addresses that very issue.

I wanted to do a mail merge from Excel to Word. However, when the person sent me the data, it was in the wrong sequence. Instead of having the data go across the columns, the data had the information going down the rows of column A.

While you could manually fix all of these, it would be a lot of cutting and pasting. This is the perfect task for automating with a macro.
If you have never used macros before, go to Tools - Macro - Security and change the setting to Medium.
The goal is to record a tiny macro that will solve one record. You want the cell pointer to be on the name to start. The macro should move the two fields over, delete the three blank rows, and end up on the next name. This way, you can run the macro over and over.
• Put the cell pointer in A1.
• Choose Tools - Macro - Record new Macro
• Give the macro a name (no spaces allowed). Choose a shortcut key. Since you will only be using the macro for this task, something like Ctrl+a is easy to remember.

• You will see a tiny toolbar. This is the Stop Recording Toolbar. It only has two buttons. The first button is the Stop Recording button. The second button is the Relative References button. If you simply recorded the macro now, Excel would hard-code that you wanted to always move A2 to B1 and A3 to C1. This works fine for one record, but you need the macro to work for all records. Turn on Relative Recording by clicking the Relative Recording button. It is a toggle, so you want it to look like it is pressed in.

• Everything you do will be recorded. Try to use keystrokes instead of the mouse.
• Hit the down arrow to move to B2.
• Ctrl+x to cut
• Hit up arrow, right arrow to move to A1.
• Ctrl+V to paste
• Left, Left, Down, Down, Ctrl+X to cut the city row
• Up, Up, Right, Right, Ctrl+V to paste the city in column c
• Left, Left, Down to move to row 2.
• Hold down the shift key while you hit down, down to select rows 2, 3, 4
• Alt+edr and enter to choose Edit - Delete - Entire Row - OK
• At this point, you've almost finished the macro. The problem is that you have three cells selected and you just want the name selected.

• Hit the up arrow and the down arrow to select just the name of the second record.
• Click on the Stop Recording button
It is time to try out the macro. Save the workbook in case something goes wrong here.
Type Ctrl+a to run the macro once. If all went well, the macro will have fixed the next name.

If that worked, you can now hold down Ctrl+a until all of the records are fixed.


This tiny macro solved a real-life problem. The key to success was using the Relative Recording mode!


The images did not copy, but if you follow these steps you will not have a problem.

Alan

 

[ColinKJ]

You can load the txt file(s) directly into the columns you want.

Are all the adresses in the one txt file, or is each address in a different txt file.

Could you paste an example of your txt file as it is stored in the txt file.

ColinKJ

 

[Sunefar]

Thanks I will try this
"alansidman"

Ther is no way to wright the macro insted of record it..?

I will give it a try!

The .txt looks like this "ColinKJ" :

T Andersen
Sorøvej 524
4700 Næstved.

Steen Andersen
Skolegade 4
4874 Gedser.

Magny Andersen
Vinkelstrædet 2
4180 Sorø.

Connie Andersen
Abildvang 27
4180 Sorø.

Charlotte Alms............. and so on
only seperated with a (.) dot, at the end and (Enter).


/Sune

 

[alansidman]

Here's the macro, but it was written for US type address. You will need to modify and put a loop on it to repeat.

Sub FixOneRecord()
'
' FixOneRecord Macro
' Macro recorded 11/30/2007 by Alan M Sidman
'
' Keyboard Shortcut: Ctrl+a
'
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.Cut
    ActiveCell.Offset(-1, 1).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(2, -1).Range("A1").Select
    Selection.Cut
    ActiveCell.Offset(-2, 2).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(1, -2).Range("A1:A3").Select
    Selection.EntireRow.Delete
    ActiveCell.Select
End Sub

Alan

 

[ColinKJ]

Hi Sunefar,

If you put this in a standard Moduel

Code:

Sub LoadTextAdds()
Path = "C:\Documents and Settings\Sune\Skrivebord\list.txt"
b = 1
FileNumber = FreeFile()
Open Path For Input As #FileNumber
st:
b = b + 1
For a = 1 To 5
Input #FileNumber, Item
Cells(b, a) = Item
If EOF(FileNumber) Then
Close #FileNumber
GoTo endd
End If
Next a
GoTo st
endd:
End Sub

Code:

You can either assign the macro to a Key combination or run it from

Tools>Macro>Macro>LoadTextAdds

ColinKJ

 

[Sunefar]

Wauu ColinKJ It works perfekt!!

...allmost

..ther is a littel problem...

some of the adresses have a extra "thing" to it, like (Kjeldsgardsvej 35 4th)
the (4th) refers to floor to the right or left.

so allready in line 6 it all get's mixet up looking like this :

Can I do anything about that?

Thanks again!!