Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have an excel sheet (mac, Excel 2011) that contains names (1st row), job titles (2nd row), and company/institution (3rd row) in one big column. I am trying to separate them into three different columns. I've tried all the macros i could find. Please help.

So instead of reading like this, as it does now:

Joe Joe

It would read

JoeJoe    |   SomeJob   |  MyCompany
share|improve this question

migrated from Dec 1 '12 at 16:00

This question came from our site for professional and enthusiast programmers.

In cell B1: =IF(MOD(ROW(),3)=1,OFFSET($A1,COLUMN()-2,0),"") Drag to right for 3 columns, then down to the end of your data. – Tim Williams Nov 30 '12 at 22:37

Try something like this. It is important that the series of 3 stays the same for tyhis to work though.

Sub SingleRow()


        ActiveCell.Offset(-1, 1).Select
        ActiveCell.Offset(1, -1).Select
            ActiveCell.Offset(-1, 2).Select
            ActiveCell.Offset(1, -2).Select
                ActiveCell.Offset(1, 0).Select

Loop Until ActiveCell.Value = "" And ActiveCell.Offset(-1, 0).Value = ""

End Sub

I tested and this should do the trick.

share|improve this answer
One small nitpick: this is a VBA macro, not a formula. Probably the only way to do what needs to be done, but it's important to note that you have to start up the VBA Editor to enter this code. – Isaac Rabinovitch Dec 2 '12 at 0:12

@Tim Williams's solution is the elegant one but a simple solution is to add a column that repeats 1, 2 and 3. Also copy the data column twice. Delete one cell from the top of the first copy and two from the top of the second copy. Then filter on the column with numbers, deselect 1 and delete what is visible. (Can also then delete the numbers column.)

Process explained in more detail under Question SU495953.

share|improve this answer

What about "Menu Data -> Text In Columns" which separates them all by one separator character you can interactivley choose in the dialog, e.g space

share|improve this answer
Data is in rows, not concatenated in a single cell... – Tim Williams Nov 30 '12 at 22:54

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.