Seperating values in one cell

  • Thread starter Thread starter Marcus Horcher
  • Start date Start date
M

Marcus Horcher

Hi,

In a file each cell represents an answer of one user to a
questionnaire with 10 questions.
Cell A1 holds the answers of user1 for the ten questions
in the following format:
Answer1,Answer2,Answer3,...
Cells B1 for user2,...

In oder to better build statistics, graphs,.. I like to
have this cell split up in ten different cells:
B1: Answer1 of user 1
C1: Answer2 of user 1
....

Is there a worksheet function seperating single values out
of one cell that are devided by a comma?

Cheers
Marcus
 
Hi

Copy cells with data in row 1
On new sheet, right-click on cell A1 and select PasteSpecial.Transpose
Without losing selection, from menu select Data.TextToColumns, select comma
as delimiter, and Finish
When in some columns data must be numeric/dates/times, then enter a number 1
into come cell, copy it, select the range to convert, and then
PasteSpecial.Multiply. After you finish, delete the temporarily entered
number 1.


Arvi Laanemets
 
Hi Marcus

Even better, there's a built-in function in the Data menu: Text to columns.
 
I would transpose your data and then do a Text to Columns
with comma as your delimiter.

1. Select col. A and insert a column.
2. Select your data in row 1 (B1:x1) and copy it.
3. Select A1 and Edit > Paste Special > Transpose.
4. Delete column B through x.
5. Select col A.
6. Data > Text to Columns. Use comma as the delimiter.

HTH
Jason
Atlanta, GA
 
Back
Top