• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

Creating data table from single column of data in Excel

I have data that is formated in one column (All in column A).  The data is repetitive in that the data repeats itself at every fixed number of cells.  Example:

Cell A1: Name #1
Cell A2: Address #1
Cell A3: Phone #1
Cell A4: Team #1
Cell A5: Name #2
Cell A6: Address #2
Cell A7: Phone #2
Cell A8: Team #2

I would like to get this information in columns.  Is there a formula I could use and drag down the page to get it to work?  There is over 1000 rows so doing it manually is not an option,
0
marku24
Asked:
marku24
  • 2
  • 2
1 Solution
 
Dan CraciunIT ConsultantCommented:
No formula. Use Data->Text to columns.

HTH,
Dan
0
 
marku24Author Commented:
Isn't that used when the data you want to deliminate is in one cell?  how would text to columns know when the data repeats itself, in this case cell A5.
0
 
Dan CraciunIT ConsultantCommented:
Missed that :) I thought you only wanted to separate Cell AX from whatever comes after.
0
 
Saqib Husain, SyedEngineerCommented:
If you want to start the table in Column C then use this formula

=INDEX($A:$A,ROW()*4-3+COLUMN()-COLUMN($C$1))

For any other column change the $C$1 accordingly
0
 
marku24Author Commented:
worked perfectly, thanks
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now