Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have an excel sheet that contains names (1st row), job titles (2nd row), company/institution (3rd row), and addresses(4th row) in one big column.

I am trying to separate them into four different columns.

Please help.

share|improve this question

closed as unclear what you're asking by Scott, fixer1234, DavidPostill, DragonLord, smc Mar 18 at 12:53

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

    
What do you need? 1 -> 4 columns? 4 -> 1 column? –  STTR Aug 19 '13 at 14:55
    
If just to display it differently you could use pivot tables. Or are you making this as a permanent change? –  Dave Aug 19 '13 at 14:58
    
It will be a permanent change. All the data is in the 1st column and needs to be spread over 4 columns. –  James Aug 19 '13 at 15:46
2  
Could you put a sample of your data (or something that looks like it) in your question? How the elements are laid out makes a difference in how to separate them? You could either lay them out as code in the answer (indent at least 4 spaces) or put up a picture (directly or through a link to imgur.com. –  chuff Aug 19 '13 at 16:15
    
And also what is your separator ? –  shA.t Mar 17 at 18:52

2 Answers 2

Looks like what you want is Copy >> Special Paste >> Transpose. You seem to want to switch rows to columns.

From Mcrosoft:

If data is entered in columns or rows, but you want to rearrange that data into rows or columns instead, you can quickly transpose the data from one to the other.

For example, the regional sales data that is organized in columns appears in rows after transposing the data, as shown in the following graphics.

Regional data in columns

Regional data in rows

1 - On the worksheet, do the following:

  • To rearrange data from columns to rows, select the cells in the columns that contain the data.
  • To rearrange data from rows to columns, select the cells in the rows that contain the data.

2 - On the Home tab, in the Clipboard group, click Copy.

Excel Ribbon Image

Keyboard shortcut To copy the selected data, you can also press CTRL+C.

Note You can only use the Copy command to rearrange the data. To complete this procedure successfully, do not use the Cut command.

3 - On the worksheet, select the first cell of the destination rows or columns into which you want to rearrange the copied data.

Note Copy areas and paste areas cannot overlap. Make sure that you select a cell in a paste area that falls outside of the area from which you copied the data.

4 - On the Home tab, in the Clipboard group, click the arrow below Paste, and then click Transpose. 5 - After the data is transposed successfully, you can delete the data in the copy area.

share|improve this answer

When I read your question, I understood it to mean that you have

                                                         

and you want

                                enter image description here

Assuming your data are in Column A, starting in cell A1, set up

  • B1=FIND(CHAR(10), $A1)
  • C1=FIND(CHAR(10), $A1, B1+1)
  • D1=FIND(CHAR(10), $A1, C1+1)
  • E1=LEFT($A1, B1-1)
  • F1=MID($A1, B1+1, C1-B1-1)
  • G1=MID($A1, C1+1, D1-C1-1)
  • H1=RIGHT($A1, LEN($A1)-D1)

Then, for the example data I provided for A1, you will get the following results:

  • B113
  • C123
  • D132
  • E1Harold Smith
  • F1President
  • G1ABC Corp
  • H1123 Main St

CHAR(10) gives an ASCII line separator character.  Columns B, C, and D are helper columns that find the offsets (locations) of the newlines in the data in Column A; then Columns E through H break the data into pieces around those locations.  Then, since you want to make a permanent change, do a copy and paste values.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.