Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import comma delimited text | Excel Discussion (Misc queries) | |||
Paste comma delimited text into a spread sheet | Excel Discussion (Misc queries) | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
importing multiple text files URGENT!!! HELP | Excel Worksheet Functions | |||
importing multiple text files??? | Excel Discussion (Misc queries) |