Comma Separated values in Rows and columns

[abhijeet_gole]

Dear friends,
I am facing a lot of problem in creating a table out of comma separated values as illustrated below
1,2,3,4,5;
6,7,8,9,10;

Here as you can see the delimiter ',' is representing the columns ( I was able to resolve that) and ";" are representing the next row record ( This is a still a problem) can any one give me a macro which can generate appropriate rows and columns for the above mentioned values where the number of columns and row may vary

[Ed_Collins]

One idea. Maybe you're aware of this and maybe not.

Save those values as shown in a text file (use TextPad or NotePad) and give the file a .csv extension. For example, call it test.csv.

Then, OPEN it with Excel. (Don't paste it in.) When you open it, Excel will recognize and understand that the commas are a delimiter. The values will each be in different columns and rows, as desired.

The semicolon in the text (csv) is not needed. If you choose to leave it in, the final column will have the value AND the semicolon. Thus, prior to saving the text (csv) file, simply do a search and replace and replace all semicolons in the file with "nothing."

OR you can extract them with a formula in the Excel file. For example, =LEFT(E4,LEN(E4)-1) would work, assuming the cell with the semicolon is in cell e5.

[abhijeet_gole]

@Ed_Collins :I have made a questionnaire using google forms, Now google forms doesnt support answers inserted in form of a table, so created paragraph type answers instead and insisted to the audience to give answers in comma separated form; so that later using a macro i can create separate sheets of these tables for further analysis. The form is mixture of one liners, multiple choice questions and paragraph type question. I need to select only specific columns with comma separated entries and change the same to table in a separate sheet for further analysis. (Also, i will am removing the ';' separator for the new line so that the entry can be done using simple "Return carriage"

[jindon]

Please Login or Register  to view this content.

[abhijeet_gole]

@jindon thanks a million, i am now working on creating separate tables in separate sheets. This script is really very helpful thanks

[jindon]

Here's a link for alternative method due to Evaluate method only accepts up to 255 characters.

http://www.excelforum.com/excel-prog...ded-array.html