separate data with semi colons

**dan**

New Member
Joined
Oct 14, 2006
Messages
5
hi
i have an excel doc with 1800 email addresses and i need to transfer the data into a file where the addresses are separated not in rows but with semi colons between the addresses
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Err, so you are saying you want 1800 email addresses in one row, separated by semi-colons? Really?!?!?
 

**dan**

New Member
Joined
Oct 14, 2006
Messages
5
yes, i need to paste them in outlook so they need to be separated by semi colons
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
I would think that you'd need to put this in a string in the first cell, but there is the limit of 32,767 characters max as the contents of the cell. I don't suppose you know the total number of characters of the list ( + 1799 extra for the semi-colons )?
 

**dan**

New Member
Joined
Oct 14, 2006
Messages
5

ADVERTISEMENT

it actually dont need to have the result in excel. It would be fine to have the addresses separated with semi colons in a text file.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Writing a macro to stuff them all into one row of an output file still has the limit of 32,767 characters ...

Help says:
Open pathname For mode [Access access] [lock] As [#]filenumber [Len=reclength]

The Open statement syntax has these parts:

Part Description
pathname Required. String expression that specifies a file name¾may include directory or folder, and drive.
mode Required. Keyword specifying the file mode: Append, Binary, Input, Output, or Random. If unspecified, the file is opened for Random access.
access Optional. Keyword specifying the operations permitted on the open file: Read, Write, or Read Write.
lock Optional. Keyword specifying the operations permitted on the open file by other processes: Shared, Lock Read, Lock Write, and Lock Read Write.
filenumber Required. A valid file number in the range 1 to 511, inclusive. Use the FreeFile function to obtain the next available file number.
reclength Optional. Number less than or equal to 32,767 (bytes). For files opened for random access, this value is the record length. For sequential files, this value is the number of characters buffered.
 

hsk

Well-known Member
Joined
Oct 19, 2006
Messages
567
List all the email address from A1 to A1800

then Put cell B1 = A1
And cell B2 =concatenate(B1,";",A2)

Copy the cell B2 till 1800 row,
B1800 will give all the email addresses in the form u want
 

Forum statistics

Threads
1,136,878
Messages
5,678,320
Members
419,755
Latest member
eshnider21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top