When you use the Microsoft Excel products listed at the bottom of this article, you can use a worksheet formula to covert data that spans multiple rows and columns to a database format (columnar).
The following example converts every four rows of data in a column to four columns of data in a single row (similar to a database field and record layout). This is a similar scenario as that which you experience when you open a worksheet or text file that contains data in a mailing label format.
- In a new worksheet, type the following data:A1: Smith, John
A2: 111 Pine St.
A3: San Diego, CA
A4: (555) 128-549
A5: Jones, Sue
A6: 222 Oak Ln.
A7: New York, NY
A8: (555) 238-1845
A9: Anderson, Tom
A10: 333 Cherry Ave.
A11: Chicago, IL
A12: (555) 581-4914
- Type the following formula in cell C1:=OFFSET($A$1,(ROW()-1)*4+INT((COLUMN()-3)),MOD(COLUMN()-3,1))
- Fill this formula across to column F, and then down to row 3.
- Adjust the column sizes as necessary. Note that the data is now displayed in cells C1 through F3 as follows:
The formula can be interpreted as
Smith, John 111 Pine St. San Diego, CA (555) 128-549
Jones, Sue 222 Oak Ln. New York, NY (555) 238-1845
Anderson, Tom 333 Cherry Ave. Chicago, IL (555) 581-4914
- f_row = row number of this offset formula
- f_col = column number of this offset formula
- rows_in_set = number of rows that make one record of data
- col_in_set = number of columns of data