Importing Comma Seperated Text Please Help ?
 

Hi There,

I have a device which pumps out text in a "serial stream" the data is in a
format like below:

1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0

Using the text import wizard the data must be arranged into columns or a
table like:

1,2,3,4,5,6,7,8,9,0
1,2,3,4,5,6,7,8,9,0

for it to import into cells, is ther any way I can get the data to format
into cells correctly ?

Anyy help greatly appreciated.

Cheers!

Byron

Byron,

Your main problem is that you appear to have no line seperator
character, though your example shows 10 columns for a line. (10 is thus
the number of Find statements used)

Hopefully someone has a better idea, but for a small amount of data you
could use:

Assuming the data is in cell A15 (insert a few rows will do that)

put in cell B16 the formula:


=FIND(",",A15,1+FIND(",",A15,1+FIND(",",A15,1+FIND (",",A15,1+FIND(",",A15,1+FIND(",",A15,1+FIND(",", A15,1)))))))

and in cell A16 the formula:

=MID(A15,1+FIND(",",A15,1+FIND(",",A15,1+FIND(",", A15,1+B16))),32768)

and formula-drag these down to cover enough data.

then hilight A16:A9999 down the column (to the end of your data) and do
CTRL/C (Copy) and Paste Special, Values to cell A1 on a new sheet

On this new sheet,
Select column A and do 'Data' - Text-to-Columns, using comma as a
delimeter

After this remove columns K:IV

also, check out the last couple of rows.

Limits are, of course, 32768 characters in a cell, so your original
data cannot exceed 32k.

Let me know how you go



Byron Wrote:
Hi There,

I have a device which pumps out text in a "serial stream" the data is
in a
format like below:

1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0

Using the text import wizard the data must be arranged into columns or
a
table like:

1,2,3,4,5,6,7,8,9,0
1,2,3,4,5,6,7,8,9,0

for it to import into cells, is ther any way I can get the data to
format
into cells correctly ?

Anyy help greatly appreciated.

Cheers!

Byron


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=396036