I have a text file of several hundred email addresses, which I want to import
into Excel (or Access). The data look like:

; ; ... etc

but when I use the Import External Data wizard, it imports the whole lot as
columns in the first row, and cuts off the last few hundred, as there are
more than 255 records, so what I get looks like:

A / B /
C /
1/ / /

How can I import them as single column entries in many rows? What I want is
this:

A /
1/ /
2/ /
3/ /

Thanks in anticipation,

Phil

Hi Phil,

To import your .txt file as rows instead of columns in Excel, you can follow these steps:

1. Open a new Excel workbook and click on the "Data" tab in the ribbon.
2. Click on "From Text/CSV" in the "Get & Transform Data" section.
3. In the "Import Data" dialog box, locate and select your .txt file, then click "Import".
4. In the "Text Import Wizard - Step 1 of 3" dialog box, select "Delimited" and click "Next".
5. In the "Text Import Wizard - Step 2 of 3" dialog box, select "Semicolon" as the delimiter and make sure the "Treat consecutive delimiters as one" checkbox is unchecked. You should see your data previewed in the "Data preview" section as a single column. Click "Next".
6. In the "Text Import Wizard - Step 3 of 3" dialog box, you can leave the default settings and click "Finish".
7. In the "Import Data" dialog box, choose where you want to import the data (e.g. a new worksheet) and click "OK".

Your data should now be imported into Excel as a single column with each email address in a separate row. You can then format the column as needed (e.g. remove the "[email]" tags) and save the file.

Import your text file into Word first and use Find & Replace (CTRL-H)
to replace the semicolons with a manual line break. Then save the file
as a text file again and then import it into Excel.

Hope this helps.

Pete

On Sep 12, 3:08 pm, Phil Zack <Phil
wrote:
I have a text file of several hundred email addresses, which I want to import
into Excel (or Access). The data look like:

; ; ... etc

but when I use the Import External Data wizard, it imports the whole lot as
columns in the first row, and cuts off the last few hundred, as there are
more than 255 records, so what I get looks like:

A / B /
C /
1/ / /

How can I import them as single column entries in many rows? What I want is
this:

A /
1/ /
2/ /
3/ /

Thanks in anticipation,

Phil

Phil,

Try this subroutine. Just edit the line that defines the path to your
actual text file.

_______________________________________

Sub GetEmailList()
Const ForReading = 1

strTxtFilePath = "C:\Test\AddyList.txt"

Set FSO = CreateObject("Scripting.FileSystemObject")
Set objFile = FSO.OpenTextFile(strTxtFilePath, ForReading)

strText = objFile.ReadAll
objFile.Close

strText = Replace(strText, vbCrLf, "")
arrText = Split(strText, ";")

For R = 0 To UBound(arrText)
Cells(R + 1, 1).Value = Trim(arrText(R))
Next R

Set FSO = Nothing
End Sub
_______________________________________

Steve




"Phil Zack" <Phil wrote in message
...
I have a text file of several hundred email addresses, which I want to
import
into Excel (or Access). The data look like:

; ; ... etc

but when I use the Import External Data wizard, it imports the whole lot
as
columns in the first row, and cuts off the last few hundred, as there are
more than 255 records, so what I get looks like:

A / B /
C /
1/ / /

How can I import them as single column entries in many rows? What I want
is
this:

A /
1/ /
2/ /
3/ /

Thanks in anticipation,

Phil

That sounds like it will do the trick Pete, but how do I enter 'manual line
break' into the Find & Replace dialogue box? If I just press Enter, teh
cursor skips out of the box, leaving it blank. Is there some sort of machine
code for Manual Line Break?

Cheers,

Phil

"Pete_UK" wrote:

Import your text file into Word first and use Find & Replace (CTRL-H)
to replace the semicolons with a manual line break. Then save the file
as a text file again and then import it into Excel.

Hope this helps.

Pete

On Sep 12, 3:08 pm, Phil Zack <Phil
wrote:
I have a text file of several hundred email addresses, which I want to import
into Excel (or Access). The data look like:

; ; ... etc

but when I use the Import External Data wizard, it imports the whole lot as
columns in the first row, and cuts off the last few hundred, as there are
more than 255 records, so what I get looks like:

A / B /
C /
1/ / /

How can I import them as single column entries in many rows? What I want is
this:

A /
1/ /
2/ /
3/ /

Thanks in anticipation,

Phil

Thanks Steve,

Sorry to sound dim, but what DO I actually do with this program?

Do I paste it somewhere and click run, or do I need to go into DOS and type
it in, or what?

Thanks,

Phil

"Steve Yandl" wrote:

Phil,

Try this subroutine. Just edit the line that defines the path to your
actual text file.

_______________________________________

Sub GetEmailList()
Const ForReading = 1

strTxtFilePath = "C:\Test\AddyList.txt"

Set FSO = CreateObject("Scripting.FileSystemObject")
Set objFile = FSO.OpenTextFile(strTxtFilePath, ForReading)

strText = objFile.ReadAll
objFile.Close

strText = Replace(strText, vbCrLf, "")
arrText = Split(strText, ";")

For R = 0 To UBound(arrText)
Cells(R + 1, 1).Value = Trim(arrText(R))
Next R

Set FSO = Nothing
End Sub
_______________________________________

Steve




"Phil Zack" <Phil wrote in message
...
I have a text file of several hundred email addresses, which I want to
import
into Excel (or Access). The data look like:

; ; ... etc

but when I use the Import External Data wizard, it imports the whole lot
as
columns in the first row, and cuts off the last few hundred, as there are
more than 255 records, so what I get looks like:

A / B /
C /
1/ / /

How can I import them as single column entries in many rows? What I want
is
this:

A /
1/ /
2/ /
3/ /

Thanks in anticipation,

Phil

In the Replace panel in Word click on "More", then click on "Special"
and then choose "Manual Line Break" from the list - you will get a
funny symbol in the Replace With box, looking like ^|. In the Find
What box you want to put your semicolon.

Click Replace All, then save the file back as a text file for use in
Excel.

Hope this helps.

Pete

On Sep 12, 3:54 pm, Phil Zack
wrote:
That sounds like it will do the trick Pete, but how do I enter 'manual line
break' into the Find & Replace dialogue box? If I just press Enter, teh
cursor skips out of the box, leaving it blank. Is there some sort of machine
code for Manual Line Break?

Cheers,

Phil



"Pete_UK" wrote:
Import your text file into Word first and use Find & Replace (CTRL-H)
to replace the semicolons with a manual line break. Then save the file
as a text file again and then import it into Excel.

Hope this helps.

Pete

On Sep 12, 3:08 pm, Phil Zack <Phil
wrote:
I have a text file of several hundred email addresses, which I want to import
into Excel (or Access). The data look like:

; ; ... etc

but when I use the Import External Data wizard, it imports the whole lot as
columns in the first row, and cuts off the last few hundred, as there are
more than 255 records, so what I get looks like:

A / B /
C /
1/ / /

How can I import them as single column entries in many rows? What I want is
this:

A /
1/ /
2/ /
3/ /

Thanks in anticipation,

Phil- Hide quoted text -

- Show quoted text -

That's it Pete, problem solved!

Thanks very much.

Phil

"Pete_UK" wrote:

In the Replace panel in Word click on "More", then click on "Special"
and then choose "Manual Line Break" from the list - you will get a
funny symbol in the Replace With box, looking like ^|. In the Find
What box you want to put your semicolon.

Click Replace All, then save the file back as a text file for use in
Excel.

Hope this helps.

Pete

On Sep 12, 3:54 pm, Phil Zack
wrote:
That sounds like it will do the trick Pete, but how do I enter 'manual line
break' into the Find & Replace dialogue box? If I just press Enter, teh
cursor skips out of the box, leaving it blank. Is there some sort of machine
code for Manual Line Break?

Cheers,

Phil



"Pete_UK" wrote:
Import your text file into Word first and use Find & Replace (CTRL-H)
to replace the semicolons with a manual line break. Then save the file
as a text file again and then import it into Excel.

Hope this helps.

Pete

On Sep 12, 3:08 pm, Phil Zack <Phil
wrote:
I have a text file of several hundred email addresses, which I want to import
into Excel (or Access). The data look like:

; ; ... etc

but when I use the Import External Data wizard, it imports the whole lot as
columns in the first row, and cuts off the last few hundred, as there are
more than 255 records, so what I get looks like:

A / B /
C /
1/ / /

How can I import them as single column entries in many rows? What I want is
this:

A /
1/ /
2/ /
3/ /

Thanks in anticipation,

Phil- Hide quoted text -

- Show quoted text -

You're welcome, Phil - thanks for feeding back.

Pete

On Sep 12, 5:20 pm, Phil Zack
wrote:
That's it Pete, problem solved!

Thanks very much.

Phil



"Pete_UK" wrote:
In the Replace panel in Word click on "More", then click on "Special"
and then choose "Manual Line Break" from the list - you will get a
funny symbol in the Replace With box, looking like ^|. In the Find
What box you want to put your semicolon.

Click Replace All, then save the file back as a text file for use in
Excel.

Hope this helps.

Pete

On Sep 12, 3:54 pm, Phil Zack
wrote:
That sounds like it will do the trick Pete, but how do I enter 'manual line
break' into the Find & Replace dialogue box? If I just press Enter, teh
cursor skips out of the box, leaving it blank. Is there some sort of machine
code for Manual Line Break?

Cheers,

Phil

"Pete_UK" wrote:
Import your text file into Word first and use Find & Replace (CTRL-H)
to replace the semicolons with a manual line break. Then save the file
as a text file again and then import it into Excel.

Hope this helps.

Pete

On Sep 12, 3:08 pm, Phil Zack <Phil
wrote:
I have a text file of several hundred email addresses, which I want to import
into Excel (or Access). The data look like:

; ; ... etc

but when I use the Import External Data wizard, it imports the whole lot as
columns in the first row, and cuts off the last few hundred, as there are
more than 255 records, so what I get looks like:

A / B /
C /
1/ / /

How can I import them as single column entries in many rows? What I want is
this:

A /
1/ /
2/ /
3/ /

Thanks in anticipation,

Phil- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Phil,

Copy what is between the lines so it's ready to paste.

Open a new Excel workbook.

Hold down 'Alt' and press the 'F11' key.

In the upper left of the Visual Basic Editor window, you will find a smaller
project window with a tree type representation of workbook and sheets. The
top level will probably read "VBAProject (Book1)". Right click the
'VBAProject (Book1)' and choose 'Insert Module'. When you do so, a new
folder appears named 'Modules' along with 'Module 1' which you are now in.
The large empty window to the right is the code window. Paste what I
provided into that code window.

Edit the line that reads:
strTxtFilePath = "C:\Test\AddyList.txt"
so that it points to the actual text file where the semi-colon separated
email addresses reside or you can create a folder "C:\Test' on your system,
move a copy of your text file into that folder and rename it AddyList.txt if
you prefer.

Close the Microsoft Visual Basic window and you will be back in your empty
workbook. Now, press 'Alt' plus 'F8' and you will find your macros in a
list. If you have more macros than the one we just pasted, make sure that
"GetMailList()" is the one selected and then click the 'Run' button. Now,
just sit back and let the subroutine deliver.

Steve



"Phil Zack" wrote in message
...
Thanks Steve,

Sorry to sound dim, but what DO I actually do with this program?

Do I paste it somewhere and click run, or do I need to go into DOS and
type
it in, or what?

Thanks,

Phil

"Steve Yandl" wrote:

Phil,

Try this subroutine. Just edit the line that defines the path to your
actual text file.

_______________________________________

Sub GetEmailList()
Const ForReading = 1

strTxtFilePath = "C:\Test\AddyList.txt"

Set FSO = CreateObject("Scripting.FileSystemObject")
Set objFile = FSO.OpenTextFile(strTxtFilePath, ForReading)

strText = objFile.ReadAll
objFile.Close

strText = Replace(strText, vbCrLf, "")
arrText = Split(strText, ";")

For R = 0 To UBound(arrText)
Cells(R + 1, 1).Value = Trim(arrText(R))
Next R

Set FSO = Nothing
End Sub
_______________________________________

Steve




"Phil Zack" <Phil wrote in message
...
I have a text file of several hundred email addresses, which I want to
import
into Excel (or Access). The data look like:

; ; ... etc

but when I use the Import External Data wizard, it imports the whole
lot
as
columns in the first row, and cuts off the last few hundred, as there
are
more than 255 records, so what I get looks like:

A / B /
C /
1/ / /

How can I import them as single column entries in many rows? What I
want
is
this:

A /
1/ /
2/ /
3/ /

Thanks in anticipation,

Phil