I have a page of data that every field is separeted by a comma with no
*unique* delimiter at the end of a record (5 fields). When I load in
excel it produces one VERY LONG row which I can not use. Is there some
way that I can *limit* the number of columns to 5 so that it will be
forced to start a new row (like a carriage return) after 5 field
entries? I guess it's obvious that I have almost no experience with
Excel - lol.
Thank you for your help,
Gary

Gary,

Have you tried Text-to-columns?

HTH
Kostis Vezerides


moussant wrote:
I have a page of data that every field is separeted by a comma with no
*unique* delimiter at the end of a record (5 fields). When I load in
excel it produces one VERY LONG row which I can not use. Is there some
way that I can *limit* the number of columns to 5 so that it will be
forced to start a new row (like a carriage return) after 5 field
entries? I guess it's obvious that I have almost no experience with
Excel - lol.
Thank you for your help,
Gary

Hi, Gary-
It sounds like you're importing an external file of some type into
Excel, and this external file has 5 fields per row. But for whatever
reason when you bring it into Excel all the individual rows get jammed
into one line. Is that correct? If yes, please tell us what type of
file you're trying to import, and post some example rows (if you can do
so without revealing sensitive proprietary data, etc.).

You may be able (with help from this forum) to write a quick macro that
will open the file, add that delimiter or whatever is required, and
write the results to a new file that will import properly.

Dave O

Thanks to both of you. I don't know what text to columns is (I just saw
import from text file) but I will go and explore as soon as I finish
this message.
Dave, not quite. It is an external file but unfortunately it does not
have 5 fields per row although it should. The external file is one
continuous, comma separated line. However, the five fields keep
repeating such as:
Name,Address,Phone,Email,Date,Name,Address,Phone,E mail,Date,Name etc.
So, when imported into Excel, they display the same way, in one long
line. I'm looking for a way to import and then display:
Name,Address,Phone,Email,Date
Name,Address,Phone,Email,Date
Name,Address,Phone,Email,Date
and thought maybe there was some way to "fix" the number of columns to
5 so that it would be forced to drop down to the next line (row) after
5 entries.
Thank you,
Gary

So you also have a comma after the 5th field of the record and before
next record starts? Or is the date stuck to the name of the next
record?

Kostis

moussant wrote:
Thanks to both of you. I don't know what text to columns is (I just saw
import from text file) but I will go and explore as soon as I finish
this message.
Dave, not quite. It is an external file but unfortunately it does not
have 5 fields per row although it should. The external file is one
continuous, comma separated line. However, the five fields keep
repeating such as:
Name,Address,Phone,Email,Date,Name,Address,Phone,E mail,Date,Name etc.
So, when imported into Excel, they display the same way, in one long
line. I'm looking for a way to import and then display:
Name,Address,Phone,Email,Date
Name,Address,Phone,Email,Date
Name,Address,Phone,Email,Date
and thought maybe there was some way to "fix" the number of columns to
5 so that it would be forced to drop down to the next line (row) after
5 entries.
Thank you,
Gary

This code will take that enormous string and parse it into 5 fields per
row. It's not pretty but will do the job. It works by counting commas
and parsing the data into a new file, which you can then import. It
will not change the original file in any way. Note for the lines in
the code that start with "Open" you'll need to specify your path and
file name.


Sub Parse_File()
Dim Lyne As String
Dim FoundComma As Byte
Dim K As Long, Z As Long, Start As Long, LastStart
Start = 1

Open "c:\ok2del.txt" For Input As #1
Open "c:\import.txt" For Output As #2

Do While Not EOF(1)
Line Input #1, Lyne
Lyne = Trim(Lyne)
If Right(Lyne, 1) < "," Then Lyne = Lyne & ","
'begin a parsing loop
Z = 0
Do
Z = Z + 1
K = K + 1
If Mid(Lyne, Z, 1) = "," Then FoundComma = FoundComma + 1
If FoundComma = 5 Then
Print #2, Mid(Lyne, Start, K)
MsgBox Start & " " & K
LastStart = LastStart + K
Start = LastStart + 1
FoundComma = 0
K = 0
End If
Loop Until Z = Len(Lyne)
Loop
Close #1
Close #2

End Sub

Since I will be going soon, here is a macro to break the text.
Assumptions, which you can correct in the code:
Imported data is in sheet "Import" in cell "A1".
Destination sheet is sheet "Data"

Sub split()
Dim destSheet As Object
source = Sheets("Import").Range("A1").Value
Set destSheet = Sheets("Data")
i = 1
rowNum = 1
While source < ""
nextcomma = InStr(1, source, ",")
If nextcomma Then
nxt = Left(source, nextcomma - 1)
source = Mid(source, nextcomma + 1, Len(source))
Else
nxt = source
source = ""
End If
destSheet.Cells(rowNum, i) = nxt
If i Mod 5 = 0 Then
i = 1
rowNum = rowNum + 1
Else
i = i + 1
End If
Wend
End Sub

HTH
Kostis Vezerides


moussant wrote:
Thanks to both of you. I don't know what text to columns is (I just saw
import from text file) but I will go and explore as soon as I finish
this message.
Dave, not quite. It is an external file but unfortunately it does not
have 5 fields per row although it should. The external file is one
continuous, comma separated line. However, the five fields keep
repeating such as:
Name,Address,Phone,Email,Date,Name,Address,Phone,E mail,Date,Name etc.
So, when imported into Excel, they display the same way, in one long
line. I'm looking for a way to import and then display:
Name,Address,Phone,Email,Date
Name,Address,Phone,Email,Date
Name,Address,Phone,Email,Date
and thought maybe there was some way to "fix" the number of columns to
5 so that it would be forced to drop down to the next line (row) after
5 entries.
Thank you,
Gary

Argh! I just realized I left a debugging line in the code I sent:
please remove or comment out the line that starts with "Msgbox".