Importing Comma Seperated Text Please Help ?

B

Byron

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
 
B

Bryan Hessey

Byron,

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

Hopefully someone has a better idea, but for a small amount of data yo
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 d
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
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 origina
data cannot exceed 32k.

Let me know how you go
 
Top